Packages

PROCEDURE DBMS_MONITOR.SESSION_TRACE_ENABLE

This procedure, which was introduced in Oracle 10.1, enables event 10046 trace in the specified session

If the SESSION_ID is NULL and SERIAL_NUM is NULL then trace is enabled in the current session

If the SESSION_ID is NOT NULL and SERIAL_NUM is NULL specified then trace is enabled in a session with the specified SESSION_ID

If the SESSION_ID is NOT NULL and SERIAL_NUM is NOT NULL then trace is enabled in the specified SESSION_ID/SID

If SESSION_ID is NULL and SERIAL_NUM is NOT NULL an error is returned

There are two optional Boolean parameters, waits and binds, both of which default to FALSE.

In Oracle 10.2 (at least) the default parameters enable event 10046 level 8 (waits) trace

For example to enable event 10046, level 8 trace in the current session use

    EXECUTE dbms_monitor.session_trace_enable

This is equivalent to

    ALTER SESSION SET EVENTS
    '10046 trace name context forever, level 1';

To enable event 10046, level 4 trace (bind variables) in the current session

    EXECUTE dbms_monitor.session_trace_enable (binds=>true);

    ALTER SESSION SET EVENTS
    '10046 trace name context forever, level 4';

To enable event 10046, level 8 trace (waits) in the current session

    EXECUTE dbms_monitor.session_trace_enable (waits=>true);

    ALTER SESSION SET EVENTS
    '10046 trace name context forever, level 8';

To enable event 10046, level 1 trace in a session with SID 9, serial number 29 use

    EXECUTE dbms_monitor.session_trace_enable (9, 29);

This is equivalent to

    EXECUTE dbms_system.set_ev (9, 29, 10046, 1, '');

To enable event 10046, level 4 trace (bind variables) in a session with SID 9, serial number 29 use

    EXECUTE dbms_monitor.session_trace_enable (9, 29, binds=>true);

    EXECUTE dbms_system.set_ev (9, 29, 10046, 4, '');

To enable event 10046, level 8 trace (waits) in a session with SID 9, serial number 29 use

    EXECUTE dbms_monitor.session_trace_enable (9, 29, waits=>true);

    EXECUTE dbms_system.set_ev (9, 29, 10046, 8, '');