PLSQL önemli özellikler -4

7-DBMS_SESSION.SET_SQL_TRACE

Bu paket sayesin yazılan kodları takip edebiliriz böylece sistem olan maliyetimizi hesap etmemiz cok daha kolay olacaktır. Butün sql leride takip etmemizi sağlar, current session takibinide sağlar.
DBMS_SESSION.SET_SQL_TRACE(sql_trace BOOLEAN);

CREATE OR REPLACE PROCEDURE core_process IS
BEGIN
IF USER = ‘PLSQL_USER’ THEN
DBMS_SESSION.SET_SQL_TRACE(TRUE);
DBMS_OUTPUT.PUT_LINE(‘Tracing açıldı…’);
END IF;

DBMS_SESSION.SET_SQL_TRACE(FALSE);
DBMS_OUTPUT.PUT_LINE(‘Trace kapatıldı.’);
END;
/

8-UTL_FILE/DBMS_OUTPUT

Bu paketler sayesinde oracledaki bir sorgumuzun neticesini bir operating sistem dosyasına kaydedebiliriz. dbms_output sayesinde ise plsql paketimizdeki bir bilgiyi cıktı olarak buffer alan sayesinde dısarı yazdırmamız mümkündür.

UTL_FILE paketini kullanmak için ilk önce bir directory yaratmamız gerekir.

CREATE OR REPLACE DIRECTORY ‘TEMP_DIR’ AS ‘/usr/users/oracle’;
GRANT READ, WRITE ON DIRECTORY TEMP_DIR TO plsql_user;

DBMS_output öreneği olarakda

begin
dbms_output.put_line(‘merhaba’);
end;
/

merhaba

çıktısını verir.

9-DBMS_METADATA

Get_ddl proceduru yaygın olarak kullanılır.

CREATE TABLE temp
(temp VARCHAR2(10) NOT NULL);

SET LONG 1000000
SELECT dbms_metadata.get_ddl(‘TABLE’, table_name)
FROM user_tables
WHERE table_name = ‘TEMP’;
DBMS_METADATA.GET_DDL(‘TABLE’,TABLE_NAME)
————————————————————————–
CREATE TABLE “PLSQL_USER”.”TEMP”
( “TEMP” VARCHAR2(10) NOT NULL ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE “SYSTEM”

CREATE OR REPLACE PROCEDURE abc AS
BEGIN
NULL;
END abc;
/

SET LONG 1000000
SELECT dbms_metadata.get_ddl(object_type, object_name)
FROM user_objects
WHERE object_name = ‘ABC’;
DBMS_METADATA.GET_DDL(‘PROCEDURE’,OBJECT_NAME)
————————————————————–
CREATE OR REPLACE PROCEDURE “PLSQL_USER”.”ABC” AS
BEGIN
NULL;
END abc;
/

SET LONG 1000000
select dbms_metadata.get_ddl(‘TABLE’,’EMP’,’SCOTT’) from dual;

CREATE TABLE “SCOTT”.”EMP”
( “EMPNO” NUMBER(4,0),
“ENAME” VARCHAR2(10),
“JOB” VARCHAR2(9),
“MGR” NUMBER(4,0),
“HIREDATE” DATE,
“SAL” NUMBER(7,2),
“COMM” NUMBER(7,2),
“DEPTNO” NUMBER(2,0),
CONSTRAINT “PK_EMP” PRIMARY KEY (“EMPNO”)
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE “USERS” ENABLE,
CONSTRAINT “FK_DEPTNO” FOREIGN KEY (“DEPTNO”)
REFERENCES “SCOTT”.”DEPT” (“DEPTNO”) ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE “USERS”;
/

execute dbms_metadata.set_transform_param(dbms_metadata.session_transform,
‘SEGMENT_ATTRIBUTES’, false)
select dbms_metadata.get_ddl(‘TABLE’,’EMP’,’SCOTT’) from dual
CREATE TABLE “SCOTT”.”EMP”
( “EMPNO” NUMBER(4,0),
“ENAME” VARCHAR2(10),
“JOB” VARCHAR2(9),
“MGR” NUMBER(4,0),
“HIREDATE” DATE,
“SAL” NUMBER(7,2),
“COMM” NUMBER(7,2),
“DEPTNO” NUMBER(2,0),
CONSTRAINT “PK_EMP” PRIMARY KEY (“EMPNO”) ENABLE,
CONSTRAINT “FK_DEPTNO” FOREIGN KEY (“DEPTNO”)
REFERENCES “SCOTT”.”DEPT” (“DEPTNO”) ENABLE)

GET_GRANTED_DDL de yine önemli prodecurelerindendir. bu procedure sayesinde hakları görebiliriz.

CREATE USER PLSQL_USER IDENTIFIED BY PLSQL_USER;
GRANT CONNECT, RESOURCE, DBA TO PLSQL_USER;
GRANT SELECT ON SCOTT.EMP TO PLSQL_USER;
SELECT DBMS_METADATA.GET_GRANTED_DDL(‘SYSTEM_GRANT’,’PLSQL_USER’)
FROM DUAL;
GRANT UNLIMITED TABLESPACE TO “PLSQL_USER”
SELECT DBMS_METADATA.GET_GRANTED_DDL(‘ROLE_GRANT’,’PLSQL_USER’)
FROM DUAL;
GRANT “CONNECT” TO “PLSQL_USER”
GRANT “RESOURCE” TO “PLSQL_USER”
GRANT “DBA” TO “PLSQL_USER”

SELECT DBMS_METADATA.GET_GRANTED_DDL(‘OBJECT_GRANT’,’PLSQL_USER’)
FROM DUAL;
GRANT SELECT ON “SCOTT”.”EMP” TO “PLSQL_USER”
SELECT DBMS_METADATA.GET_GRANTED_DDL(‘DEFAULT_ROLE’,’PLSQL_USER’)
FROM DUAL;
ALTER USER “PLSQL_USER” DEFAULT ROLE ALL

SELECT DBMS_METADATA.GET_GRANTED_DDL(‘ROLE_GRANT’, ‘DBA’)
FROM DUAL;
GRANT “SELECT_CATALOG_ROLE” TO “DBA” WITH ADMIN OPTION;
GRANT “EXECUTE_CATALOG_ROLE” TO “DBA” WITH ADMIN OPTION;
GRANT “DELETE_CATALOG_ROLE” TO “DBA” WITH ADMIN OPTION;

SELECT DBMS_METADATA.GET_GRANTED_DDL(‘OBJECT_GRANT’, ‘DBA’)
FROM DUAL;
GRANT ALTER ON “SYS”.”MAP_OBJECT” TO “DBA”;
GRANT SELECT ON “SYS”.”MAP_OBJECT” TO “DBA”;
GRANT FLASHBACK ON “SYS”.”MAP_OBJECT” TO “DBA”;
GRANT EXECUTE ON “SYS”.”DBMS_FLASHBACK” TO “DBA”;
SELECT DBMS_METADATA.GET_GRANTED_DDL(‘SYSTEM_GRANT’, ‘DBA’)
FROM DUAL;
GRANT CREATE ANY SQL PROFILE TO “DBA” WITH ADMIN OPTION;
GRANT ADMINISTER ANY SQL TUNING SET TO “DBA” WITH ADMIN OPTION;
GRANT DROP ANY SQL PROFILE TO “DBA” WITH ADMIN OPTION;
GRANT MANAGE SCHEDULER TO “DBA” WITH ADMIN OPTION;

10-BAGLANTI ZAMANI LOGLARI

CREATE TABLE session_logon_statistics
(sid NUMBER,
user_logged VARCHAR2(30),
start_time DATE,
end_time DATE);

CREATE OR REPLACE TRIGGER logon_log_trigger
AFTER LOGON ON DATABASE
BEGIN
INSERT INTO session_logon_statistics
(sid, user_logged, start_time)
SELECT DISTINCT sid, ora_login_user, SYSDATE
FROM v$mystat;
END;
/
CREATE OR REPLACE TRIGGER logoff_log_trigger
BEFORE LOGOFF ON DATABASE
BEGIN
UPDATE session_logon_statistics
SET end_time = SYSDATE
WHERE sid = (select distinct sid from v$mystat)
AND end_time IS NULL;
end;
/

COLUMN user_logged FORMAT a15
COLUMN start_time FORMAT a20
COLUMN end_time FORMAT a20
SELECT sid, user_logged,
TO_CHAR(start_time, ‘MM/DD/YYYY HH24:MI:SS’) start_time,
TO_CHAR(end_time, ‘MM/DD/YYYY HH24:MI:SS’) end_time
FROM session_logon_statistics
order by sid, user_logged, start_time;
SID USER_LOGGED START_TIME END_TIME
———- ————— ——————– ——————–
12 TRIGGER_TEST 01/22/2007 19:11:53 01/22/2007 19:17:22
12 TRIGGER_TEST 01/22/2007 19:17:24 01/22/2007 19:17:46
13 PLSQL_USER 01/22/2007 19:12:19 01/22/2007 19:18:13
13 SYS 01/22/2007 19:18:38 01/22/2007 19:19:34
13 SYS 01/22/2007 19:19:35 01/22/2007 19:19:53
13 SYS 01/22/2007 19:19:59
14 TRIGGER_TEST 01/22/2007 19:12:29 01/22/2007 19:18:03

Advertisements

About oracledocuments

Zekeriya Beşiroğlu, It joined the Bilginc IT Academy in July 2000. In the meantime, the Oracle Education, Oracle Data Base Management System and Oracle Internet Technologies, Oracle development technologies such as the training of Oracle products is responsible for training as consultants. During this task, since 1 April 2008 with 293 Oracle training in total, in 2150 Oracle customers successfully submitted. Oracle 10g New York in November 2002 have received training and education in Turkey was the first time the consultants. 20 April 2008 on education in Turkey 11g is the first who was a consultant. Oracle Real Application Cluster Expert advisor is certified is the first . Http://zekeriyabesiroglu.blogspot.com and Http://www.oracleforum.info owner and manager of the site. Its own has more than one hundred articles. In addition, creation and improvement of training materials are also related to work. Oracle products are used, database management and reporting Academy eruditely It also is working on. From the date of 1 January 2009 will continue to work as the Technical Director
This entry was posted in Uncategorized and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s