Trace Current Oracle Session – Different Methods

Method 1. 

Using alter session command

ALTER SESSION SET sql_trace=TRUE;
ALTER SESSION SET sql_trace=FALSE;

Example

SQL> ALTER SESSION SET sql_trace=TRUE;
 
Session altered.
 
SQL> ALTER SESSION SET sql_trace=FALSE;
 
Session altered.

advertisements

 

Method 2. 

Using DBMS_SESSION package

EXEC DBMS_SESSION.set_sql_trace(sql_trace => TRUE);
EXEC DBMS_SESSION.set_sql_trace(sql_trace => FALSE);
 
SQL> EXEC DBMS_SESSION.set_sql_trace(sql_trace => TRUE);
 
PL/SQL procedure successfully completed.
 
SQL> EXEC DBMS_SESSION.set_sql_trace(sql_trace => FALSE);
 
PL/SQL procedure successfully completed.

Method 3. 

Using Alter session set events command

ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';
ALTER SESSION SET EVENTS '10046 trace name context off';
 
SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';
 
Session altered.
 
SQL> ALTER SESSION SET EVENTS '10046 trace name context off';
 
Session altered.