DBMS_SESSION.SET_SQL_TRACE

This was tested on Oracle 11.1. In previous posts, I have looked at enabling tracing. You can start and stop a trace of your current session like this:

SQL> alter session set sql_trace = true;

 

Session altered.

 

SQL> alter session set sql_trace = false;

 

Session altered.

 

SQL>

You can start and stop tracing an entire instance as follows:

SQL> alter system set sql_trace = true;

 

System altered.

 

SQL> alter system set sql_trace = false;

 

System altered.

 

SQL>

You can also start and stop tracing an instance by setting the sql_trace initialization parameter.

 

Going through some old notes, I saw a different way of tracing your current session and decided to try it out (the first statement is just to make it easy to see the trace file):

SQL> l

  1* alter session set tracefile_identifier = 'ANDREW'

SQL> /

 

Session altered.

 

SQL> exec dbms_session.set_sql_trace(true);

 

PL/SQL procedure successfully completed.

 

SQL> select * from dual

  2  /

 

D

-

X

 

SQL> exec dbms_session.set_sql_trace(false);

 

PL/SQL procedure successfully completed.

 

SQL>

Then I looked in the directory specified by the user_dump_dest parameter and saw the associated trace file below:

Solaris > ls *ANDREW.trc

PQEDPT1_ora_27267_ANDREW.trc

Solaris >