SQL Trace及10046事件

SQL> select * from v$session where username='SCOTT';

SQL> /   


       SID    SERIAL#
---------- ----------
       364         96

SQL> exec dbms_system.set_sql_trace_in_session(364,98,true);


PL/SQL procedure successfully completed.

到session 执行SQL


SQL> exec dbms_system.set_sql_trace_in_session(364,98,false);


PL/SQL procedure successfully completed.

执行SQL的那个Session

SQL> @Get_Trace.sql;
 18  ;


TRACE_FILE
--------------------------------------------------------------------------------
/u01/dev/db/tech_st/11.1.0/admin/DEV_ebs/diag/rdbms/dev/DEV/trace/DEV_ora_4001.trc

11g之后查询更简单

SQL> select tracefile from v$process where addr=(select paddr from v$session where sid=(select distinct sid from v$mystat));


TRACEFILE
--------------------------------------------------------------------------------
/u01/dev/db/tech_st/11.1.0/admin/DEV_ebs/diag/rdbms/dev/DEV/trace/DEV_ora_4001.t
rc