10046 Event is used to trace SQL statements (similar to sql_trace=true) with additional details depending on the tracing level which is specified.
10046 EVENT levels:
1 – Enable standard SQL_TRACE functionality (Default)
4 – As Level 1 PLUS trace bind values
8 – As Level 1 PLUS trace waits
12 – As Level 1 PLUS both trace bind values and waits
There are various ways to enable the tracing
1) Using Alter session command
ALTER SESSION SET EVENTS ’10046 TRACE NAME CONTEXT FOREVER, LEVEL 12′;
To Turn it off
ALTER SESSION SET EVENTS ’10046 TRACE NAME CONTEXT OFF’;
2) Using DBMS_SESSION
EXECUTE DBMS_SESSION.SESSION_TRACE_ENABLE(waits => TRUE, binds => FALSE);
The TRUE argument specifies that wait information will be present in the trace. The FALSE argument specifies that bind information will not be present in the trace.
The SESSION_TRACE_DISABLE procedure disables the trace for the invoking session.
For example:
EXECUTE DBMS_SESSION.SESSION_TRACE_DISABLE();
3) Using DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION
EXEC SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(SID,SERIAL#,TRUE);
Where SID and SERIAL# points to the session being traced and can be retrieved from V$SESSION
To turn it off
EXEC SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(SID,SERIAL#,FALSE);
4) Using DBMS_MONITOR
Starting from Oracle 10g, there is a new package which can be used to enable tracing.
EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(SESSION_ID=>sid,SERIAL_NUM=>serial#,WAITS=>TRUE,BINDS=>TRUE)
To turn it off
EXECUTE DBMS_MONITOR.SESSION_TRACE_DISABLE(session_id => sid, serial_num => serial#);
Please refer to following documentation link for more details
http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/sqltrace.htm#i8759
5) Using ORADEBUG
connect / as sysdba
Sql>oradebug setospid 1234
Sql>oradebug unlimit
Sql>oradebug event 10046 trace name context forever,level 12
Here 1234 is the OS process Id for the session to be traced.
Sql>oradebug tracefile_name
Above command will show the location of the trace file
To disable oradebug tracing
Sql> oradebug event 10046 trace name context off
Whichever method you use, Oracle will create the trace files in the server’s user_dump_dest directory.
