sql跟踪八种方法

方法一 sql_trace
只能跟踪自己的会话

SQL> alter session set sql_trace=true;
SQL> alter session set sql_trace=false;
SQL> select * from v$diag_info;
tkprof /u01/app/oracle/diag/rdbms/emdb/emdb/trace/emdb_ora_18082.trc trc1

方法二、10046

Alter session set events '10046 trace name context forever, level 12';
Alter session set events '10046 trace name context off';

方法三、DBMS_SUPPORT
dbmssupp.sql 和 prvtsupp.plb
–创建

SQL> @?/rdbms/admin/dbmssupp.sql
Package created.
Package body created.
SQL> @?/rdbms/admin/prvtsupp.plb
Package body created.

本会话

exec dbms_support.start_trace；
exec dbms_support.stop_trace;

其它会话

exec sys.dbms_system.SET_BOOL_PARAM_IN_SESSION(134, 64265, 'sql_trace', TRUE);
exec sys.dbms_system.SET_BOOL_PARAM_IN_SESSION(134, 64265, 'sql_trace', FALSE);

方法四、dbms_system.set_ev

exec dbms_system.set_ev(134, 45851, 10046, 12, '');
exec dbms_system.set_ev(134, 45851, 10046, 0, '');

方法五、dbms_system.set_sql_trace_in_session

exec dbms_system.set_sql_trace_in_session(134,45851,TRUE);
exec dbms_system.set_sql_trace_in_session(134,45851,FALSE);

方法六、sys.dbms_monitor

exec sys.dbms_monitor.session_trace_enable(session_id=>134,serial_num=>45851, waits=>true, binds=>true);
exec sys.dbms_monitor.session_trace_disable(session_id=>134,serial_num=>45851);

方法七、oradebug

oradebug setospid 22399
oradebug event 10046 trace name context forever, level 12;
oradebug event 10046 trace name context off ;

方法八、trigger

create or replace trigger user_logon_trg
after logon on database
begin
--IF SYS_CONTEXT('USERENV','SESSION_USER') = '<USER_NM>' AND SYS_CONTEXT('USERENV','HOST') = '<HOST_NM>' THEN
if USER = 'SCOTT' then
execute immediate
'Alter session set events ''10046 trace name context forever, level 8''';
end if;
end;
/

create or replace trigger user_logoff_trg
before logoff on database
begin
if USER = 'SCOTT' then
execute immediate
'Alter session set events ''10046 trace name context off''';
end if;
end;
/

原文
General SQL_TRACE / 10046 trace Gathering Examples (Doc ID 1274511.1)