SQL Trace #oracle #sql

sqltrace.md

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 = '<USERNAME>';

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;

For example, to provide more detailed SQL trace information:

ALTER SESSION SET EVENTS '10046 trace name context forever level NN'

where NN:

1 is same as a regular trace.
4 means also dump bind variables
8 means also dump wait information
12 means dump both bind and wait information

Full stat debug

alter session set events '10053 trace name context forever'