JL Computer Consultancy

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:

        dbms_system.set_sql_trace_in_session (
复制

               id             number,
复制

               serial#        number,
复制

               sql_trace      boolean
复制

        ) 
复制

actually calls the procedure:

        dbms_system.set_ev(
复制

               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:

        dbms_system.read_ev(
复制

               iev     binary_integer,
复制

               oev     binary_integer
复制

        );
复制

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
复制

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.
复制

rem
复制

set serveroutput on size 10000
复制

 
复制

alter session set sql_trace true;
复制

 
复制

declare
复制

        iev binary_integer := 10046;
复制

        oev binary_integer := 0;
复制

begin
复制

        dbms_system.read_ev(iev,oev);
复制

        dbms_output.put_line(oev);
复制

end;
复制

.
复制

/
复制

Sample output:

SQL> start read_event
复制

 
复制

Session altered.
复制

 
复制

1
复制

 
复制

PL/SQL procedure successfully completed.
复制

 
复制

SQL>
复制

Addendum:

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.