How to enable tracing for a session

To enable trace for a session:

execute dbms_monitor.session_trace_enable(session_id => 127,
                                  serial_num => 29,
                                  waits => TRUE,
                                  binds => FALSE);

Disable as follows:

execute dbms_monitor.session_trace_disable(127,29);


Alternatively, use the dbms_system package:

execute dbms_system.set_sql_trace_in_session(17,45171,true);


When finished, disable tracing:


dbms_system.stop_trace_in_session(17,45171);


To enable the writing of a trace file for your current session:

execute dbms_session.set_sql_trace(true);

Note that the user needs the ALTER SESSION privilege to perform tracing:

GRANT ALTER SESSION TO scott;

Note 293661.1 published on MOS is another great source to use the dbms_monitor package efficiently.