Summary for Enabling SQL Trace
There are various ways to enable trace.
1. alter session set sql_trace = true;
require DBA privileage
2. alter session set events ‘10046 trace name context forever, level 8′;
3. oradebug setmypid;
4. execute dbms_session.session_trace_enable(waits default true, binds default false);
can be invoked by any user to enable session-level SQL trace for their own session.
5. execute dbms_session.set_sql_trace(sql_trace);
require DBA privileage, equivalent to "alter session set sql_trace = true"
6. exec dbms_monitor.session_trace_enable(session_id default null, serial_num default null, waits default true, binds default false);
only visable to DBA.
7. dbms_support.start_trace(sid, serial, wait default true, binds default false);
is not present by default, but can be loaded as the SYS user by executing the $ORACLE_HOME/rdbms/admin/dbmssupp.sql script.
8. dbms_system.set_sql_trace_in_session
sys.dbms_system.set_sql_trace_in_session(p_sid, p_serial#, TRUE);
The dbms_system package is not a published package in the Oracle published documentation of 9iR2, 10gR1 or 10gR2..
Method 1-5 can be used to enable SQL trace for own session.
Method 6-8 can be used to enable SQL trace for other sessions, session_id and serial# are required.