Am I running SQL_TRACE
SQL_TRACE is a well-documented facility in Oracle, and many developers are now including some sort of standard routine in their programs to allow then to switch sql_trace on selectively in production systems. (See my notes on a simple tracing package for example).
One problem with such routines however is that of determining whether or not the session you are currently running is already in trace mode. Imagine you have flagged functions A and B to run with sql_trace switched on - but function A calls function B - in many systems function B will switch off sql_trace as the function ends because it did not know (or register) the fact that sql_trace had been switched on by the caller: the net result is that function A stops tracing right after it calls function B.
It would be possible to produce a tidy work-around to this problem IF a function could determine whether or not sql_trace was already switched on. It is possible, and this note tells you how.
In an undocumented feature of Oracle (actually it is documented - the documentation says: "This is an internally used routine that should never be used by users", so you have been warned) there is a function to set ANY event you want at any level you choose, and another to read the current level of the event. Since sql_trace is synonymous with event 10046 at level 1 you can, if you dare, use the latter function to find out whether your current session is running with sql_trace true.
The relevant package is dbms_system in the script $ORACLE_HOME/rdbms/admib/dbmsutil.sql (you will find that this has been wrapped in Oracle 8 and transferred to script prvtutil.plb). If you have a version 7 copy of this script handy you will see that the procedure:
actually calls the procedure:
si binary_integer, -- sid
se binary_integer, -- serial#
ev binary_integer, -- event
le binary_integer, -- level
nm varchar2 -- name
and that the package also contains a procedure:
which is the one we are after. Even if you no longer have version 7 handy, if you 'describe dbms_system' from SQL*Plus in version 8 you will get the (complete) list of procedures and functions in the package with their lists of parameters.
Finding out if sql_trace is running for your session is easy, you simply execute a PL/SQL block something like the following - and I have switched on sql_trace in this script so that I can give you a sample of the output.
rem Script: read_event.sql
rem Author: Jonathan Lewis
rem Dated: 14th-Jan-1998
rem Purpose: Call dbms_system to check sql_trace
rem Returns the level of event 10046 for current session
rem This will be 1 if sql_trace is true.
set serveroutput on size 10000
alter session set sql_trace true;
iev binary_integer := 10046;
oev binary_integer := 0;
SQL> start read_event
PL/SQL procedure successfully completed.
Of course, now that you know how set_sql_trace_in_session works, you could call the set_ev procedure directly and set almost any event you fancy in any session you need to hit. One day, some time this millenium probably, I will be writing up the list of events that I have called upon in the past to solve problems or find out how Oracle works.
Be warned that the package is not totally reliable. Test carefully on your platform and version of Oracle before firing off these functions too casually.