How to use dbms_monitor and dbms_session to turn on trace?

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');

One comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.