Packages

PROCEDURE DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION

This procedure enables/disables trace in another session

This is the equivalent to enabling event 10046 level 1.

For example to enable trace in a session with SID 9, use

    EXECUTE dbms_system.set_sql_trace_in_session (9, 29, true);

To disable trace in the same session use

    EXECUTE dbms_system.set_sql_trace_in_session (9, 29, false);

The SID and serial# for the session can be obtained from V$SESSION e.g.

    DECLARE
        l_sid NUMBER;
        l_serial NUMBER;
    BEGIN
        SELECT sid, serial#
        INTO l_sid, l_serial
        FROM v$session
        WHERE username = 'User1';

        dbms_system.set_sql_trace_in_session (l_sid, l_serial, TRUE);
    END;
    /