Oracle Database Administration: The Essential Refe

Enabling SQL Trace for a specific session

There are two ways to enable SQL Trace for your own session, and there is also a way to turn on SQL Trace for any current session.

You can enable SQL Trace for your own session with the following SQL statement:

ALTER SESSION SET SQL_TRACE = TRUE;

Alternately, from PL/SQL, you can make the following procedure call:

DBMS_SESSION.SET_SQL_TRACE (TRUE);

Finally, you can turn on SQL Trace for any connected session by issuing the following command:

EXECUTE DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid,serial#,TRUE);

You can obtain the values of sid and serial# from the V$SESSION dynamic view by issuing the following query:

SELECT sid,serial# FROM v$session WHERE username = 'CDSTEST';

In all three cases, once SQL Trace is turned on, it can be disabled by using the same call, replacing the keyword TRUE with FALSE. For example:

ALTER SESSION SET SQL_TRACE = FALSE;