通过登录触发器跟踪会话产生trace文件,方便对测试环境进行SQL跟踪,即使执行失败的SQL也会记录到跟踪文件中,这对于排错很有用
sqlplus / as sysdba
--创建登陆后的触发器
CREATE OR REPLACE TRIGGER check_user
AFTER LOGON ON DATABASE
BEGIN
EXECUTE IMMEDIATE 'alter session set sql_trace=true';
EXECUTE IMMEDIATE 'ALTER SESSION SET tracefile_identifier= ''TRACE'' ';
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR
(-20000, 'Unexpected error: '|| DBMS_Utility.Format_Error_Stack);
END;
--创建退出登录前的触发器
CREATE OR REPLACE TRIGGER check_user_2
BEFORE LOGOFF ON DATABASE
BEGIN
EXECUTE IMMEDIATE 'alter session set sql_trace=false';
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR
(-20000, 'Unexpected error: '|| DBMS_Utility.Format_Error_Stack);
END;
--查看跟踪文件的路径
select * from v$diag_info where name = 'Diag Trace';
1 Diag Trace /u01/app/oracle/product/11.2.0/db_1/rdbms/bdump/diag/rdbms/ota4g/OTA4G/trace
跟踪文件名中带有会话的进程号
SELECT SPID FROM V$PROCESS WHERE ADDR IN ( SELECT PADDR FROM V$SESSION WHERE SID = 40 and serial# = 1561);
--查看跟踪文件
ls -trl
-rw-r----- 1 oracle oinstall 141 Dec 22 18:59 OTA4G_ora_19602_TRACE.trm
-rw-r----- 1 oracle oinstall 9049 Dec 22 18:59 OTA4G_ora_19602_TRACE.trc
[oracle@localhost trace]$ vi OTA4G_ora_19602_TRACE.trc
vi OTA4G_ora_19602_TRACE.trc
格式化生成的跟踪文件
tkprof OTA4G_ora_19602_TRACE.trc 02.txt
需要注意的是,在排错结束要及时删除对应的触发器,以免产生大量的跟踪文件
drop trigger check_user;
drop trigger check_user_2;
sqlplus / as sysdba
--创建登陆后的触发器
CREATE OR REPLACE TRIGGER check_user
AFTER LOGON ON DATABASE
BEGIN
EXECUTE IMMEDIATE 'alter session set sql_trace=true';
EXECUTE IMMEDIATE 'ALTER SESSION SET tracefile_identifier= ''TRACE'' ';
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR
(-20000, 'Unexpected error: '|| DBMS_Utility.Format_Error_Stack);
END;
--创建退出登录前的触发器
CREATE OR REPLACE TRIGGER check_user_2
BEFORE LOGOFF ON DATABASE
BEGIN
EXECUTE IMMEDIATE 'alter session set sql_trace=false';
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR
(-20000, 'Unexpected error: '|| DBMS_Utility.Format_Error_Stack);
END;
--查看跟踪文件的路径
select * from v$diag_info where name = 'Diag Trace';
1 Diag Trace /u01/app/oracle/product/11.2.0/db_1/rdbms/bdump/diag/rdbms/ota4g/OTA4G/trace
跟踪文件名中带有会话的进程号
SELECT SPID FROM V$PROCESS WHERE ADDR IN ( SELECT PADDR FROM V$SESSION WHERE SID = 40 and serial# = 1561);
--查看跟踪文件
ls -trl
-rw-r----- 1 oracle oinstall 141 Dec 22 18:59 OTA4G_ora_19602_TRACE.trm
-rw-r----- 1 oracle oinstall 9049 Dec 22 18:59 OTA4G_ora_19602_TRACE.trc
[oracle@localhost trace]$ vi OTA4G_ora_19602_TRACE.trc
vi OTA4G_ora_19602_TRACE.trc
格式化生成的跟踪文件
tkprof OTA4G_ora_19602_TRACE.trc 02.txt
需要注意的是,在排错结束要及时删除对应的触发器,以免产生大量的跟踪文件
drop trigger check_user;
drop trigger check_user_2;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26506993/viewspace-2131238/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26506993/viewspace-2131238/