Thursday, March 28, 2013

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 >

2 comments:

Laurent Schneider said...

if you issue

alter system set sql_trace=true;
alter system set sql_trace=false;

You will put an obsolete parameter in your spfile and get an ORA- error on your next startup.

Use DBMS_MONITOR instead!

Andrew Reid said...

Dear Laurent,

Thank you for your comment. I didn't know that so I will try it out.

Kind regards,

Andrew