Re: Checking SQL_TRACE status of a particular session

From: Peter Teoh <>
Date: Thu, 24 Jan 2008 22:19:12 -0800 (PST)
Message-ID: <>

Thank you Steve Howard, I have found another one:

SQL> select
  2 d.value||'/'||lower(rtrim(i.instance, chr(0)))||'_ora_'|| p.spid||'.trc' trace_file_name
  3 from
  4 ( select p.spid

  5      from sys.v$mystat m,sys.v$session s,sys.v$process p
  6      where m.statistic# = 1 and s.sid = m.sid and p.addr =
s.paddr) p,
  7 ( select t.instance from sys.v$thread t,sys.v$parameter v   8 where = 'thread' and (v.value = 0 or t.thread# = to_number(v.value))) i,
  9 ( select value from sys.v$parameter where name = 'user_dump_dest') d
 10 /

Following the interesting discussions at:

has all touch on SQL_TRACE, and how to sql_trace on, but no one mentioned that IT IS NOT "ENABLED" except in ONE rare scenario:

Here it is mentioned that:

dbms_system.set_ev, dbms_support.start_trace, dbms_monitor.session_trace_enable are procedures that sets sql_trace, sql_trace_waits, sql_trace_binds in v$session. For all other above explained methods these values are not set. Also if you try to set these values for other sessions then v$session is updated only after the other session has made some activity and some info is written in trace file.

I verified this above and found it true. But if I just issued "alter session set sql_trace=true;", and after verifying that the trace files are generated, and no matter what I do, the SQL_TRACE in V$SESSION will never change.

Sounds more like a bug to me........and everything just seems overly complex.... Received on Fri Jan 25 2008 - 00:19:12 CST

Original text of this message