sql tracing with event 10046

How to setup SQL trace in Oracle:

SQL Tracing:

Method 1:

To trace yourself:

Option 1:

ALTER SESSION SET sql_trace = true;
ALTER SESSION SET tracefile_identifier = mysqltrace;
ALTER SESSION SET sql_trace = false;

Option 2:
SQL>alter session set timed_statistics=true -- turn timing on.
SQL>alter session set max_dump_file_size=unlimited
SQL>alter session set tracefile_identifier='MYSESSION'
SQL>alter session set events '10046 trace name context forever, level N'
SQL> (((run all of your processing here)))
SQL>alter session set events '10046 trace name context off'

Trace File Location: USER_DUMP_DEST

Value for N:
N = 1: ==SQL_TRACE=true
N = 4: ==SQL_TRACE + bind variable values
N = 8: ==SQL_TRACE + wait events at the query level
N = 12: ==SQL_TRACE + both bind variables and wait events

To trace other user:

Option 1
SQL> alter system set timed_statistics = true;
SQL> execute dbms_system.set_sql_trace_in_session(8, 13607, true);
SQL> (((User SID=8, SERIAL#=13607 runs his query))))
SQL> execute dbms_system.set_sql_trace_in_session(8,13607, false);


Option 2
Note: binds=false for level 8 trace; true for level 12 trace.

SQL>execute sys.dbms_support.start_trace_in_session(&&SID, &&SERIAL, waits=>true, binds=>false);
SQL>(((user runs his query)))
SQL> execute sys.dbms_support.stop_trace_in_session(&&SID, &&SERIAL);