使用10046事件来sql进行跟踪

10046事件的说明和使用
 10046事件是oracle内部事件，是sql_trace的增强
 1、设置级别
    level 1      启用标准的sql_trace的功能  等价于sql_trace
    level 4      等价于level 1 +  邦定值
    level 8      等价于level 1 + 等待事件跟踪
    level 12     等价于level 1 + level 4 + level 8
 2、全局设置
    在参数文件spfile 中加入 event="10046 trace name context forever, level 12 "
 3、session 中设置
    SQL> SQL> alter session set events'10046 trace name context forever ';
    SQL> alter session set events'10046 trace name context forever , level 12 ';
    SQL> alter session set events '10046 trace name context off ';
 4、对其它用户session设置
    通过dbms_system.set_ev 系统包来实现
    SQL> desc dbms_system
    ......
 PROCEDURE SET_EV
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SI                             BINARY_INTEGER          IN
 SE                             BINARY_INTEGER          IN
 EV                             BINARY_INTEGER          IN
 LE                             BINARY_INTEGER          IN
 NM                             VARCHAR2                IN
 
 其中SI、SE来之v$session
 查询获得需要跟踪的session信息
 SQL> select sid ,serial# , username from v$session where username is not null;

       SID    SERIAL# USERNAME
---------- ---------- ------------------------------
       302        619 SYS
       315       1545 SYS
       335       2373 SYS
 执行跟踪
  SQL> exec dbms_system.set_ev(302,619,10046,12,'sys');
  PL/SQL procedure successfully completed. 

结束跟踪
  SQL> exec dbms_system.set_ev(302,619,10046,0,'sys');
  PL/SQL procedure successfully completed.
查找跟踪文件
 select d.value||'/'||lower(rtrim(i.instance,chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name
 from                         
 (select p.spid
 from sys.v$mystat m , sys.v$session s ,v$process p
 where m.statistic#= 1 and s.sid=m.sid and p.addr=s.paddr) p,
 (select  t.instance from sys.v$thread t , sys.v$parameter v
 where v.name = 'thread' and (v.value=0 or t.thread#=to_number(v.value))) i,
 (select value  from sys.v$parameter where name ='user_dump_dest' )  d;