DBMS_SYSTEM.SET_SQL_TRACE_IN

DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION Procedure

This procedure enables/disables trace in another session.

Argument Name	Type	In/Out	Default?
SID	NUMBER	IN
SERIAL#	NUMBER	IN
SQL_TRACE	BOOLEAN	IN

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;
/