SQL Trace and 10046 event

1. 跟踪当前会话

SQL>alter session set sql_trace=true; --开启SQL跟踪

SQL>select * from scott.emp  --执行语句

SQL>alter session set sql_trace=flash;  --关闭SQL跟踪

最后去跟踪目录查看跟踪文件 

 

2.跟踪特定会话

SQL> select SID,SERIAL# from v$session where sid='38';

       SID    SERIAL#

---------- ----------

        38        369             --查看需要跟踪的会话的sid、SERIAL#

SQL> grant execute on dbms_system to scott;    --为执行的跟踪的用户授权

Grant succeeded.

SQL> conn scott/scott

Connected.

SQL> exec sys.dbms_system.set_sql_trace_in_session(38,369,true);  --利用dbms包执行SQL跟踪

PL/SQL procedure successfully completed.

10046 Event

SQL>alter session set event '10046 trace name context forever,level 1|4|6|12'; --对会话进行10046跟踪

dbms_system.set_ev(sid,serial#,10046,level,'username')  --10046包跟踪

SQL>exec dbms_system.set_ev(38,369,10046,12,'');