Re: How to tell if a Session is being traced

There's a package called DBMS_SYSTEM owned by SYS which contains a procedure called READ_EV. This will tell you if the event 10046 (SQL_TRACE) is on (returns 1) or off (returns 0).

Example:-

declare

   event_on            binary_integer ;
   event_num         binary_integer := 10046 ;    -- trace event

   sys.dbms_system.read_ev(event_num, event_on) ;    if (event_on = 1) then

      dbms_output.put_line('Trace is on for this session') ;    else

      dbms_output.put_line('Trace is off') ;    end if ;
end ;
/

If you use SQL statements that contain bind variables and wish to know the value of the bind variable then instead of using "alter system set sql_trace = true", try "alter session set events = '10046 trace name context forever, level 4' ". This dumps the variable values to the trace file (This may be version specific but works from 7.2 upwards).

Setting SQL_TRACE = TRUE is equivalent to Level 1 Level 8 shows waits
Level 12 = Level 4 and Level 8

To switch off use "alter session set events = '10046 trace name context off' "

Hope this helps
Tony R.

Richard Creasey wrote in message <37669CA9.6F48DB9C_at_mc.duke.edu>...
>Hello All
>
> Is there a dictionary view of system table that I can query to see if
>a given session is currently being traced?
>
>Example. Monday DBA 1 executes :
>'exec dbms_system.set_sql_trace_in_session(sid,serial#,TRUE)'
>and Tuesday DBA 2 needs to see if anyone turned tracing on for
>that session already or should they do it.
>
>Thanks
>Richard
>creas002_at_mc.duke.edu
>
Received on Tue Jun 15 1999 - 15:15:06 CDT