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