Oracle DBA Daily Experiences

Using dbms_monitor.client_id_trace_enable to turn trace on for all sessions that have client identifier set to 'debug' and using dbms_session.set_identifier which will turn on trace for session.

Example:
To setup trace for client identifier
SQL> exec dbms_monitor.client_id_trace_enable('debug', true, true);

PL/SQL procedure successfully completed.

– displays list of client identifers for which trace can be turned on
SQL> select trace_type, primary_id, waits, binds from dba_enabled_traces;

TRACE_TYPE
———————
PRIMARY_ID WAITS BINDS
—————————————————————- —– —–
CLIENT_ID
debug TRUE TRUE

PL/SQL procedure successfully completed.

In another session in which you want to turn on trace
– set client identifier on a session which will turn on trace
SQL> exec dbms_session.set_identifier('debug');

PL/SQL procedure successfully completed.

SQL> select sysdate from dual;

SYSDATE
———
20-JUN-10

To turn off trace
– to disable trace
SQL> exec dbms_monitor.client_id_trace_disable('debug');