Re: Checking SQL_TRACE status of a particular session

From: Peter Teoh <htmldeveloper_at_gmail.com>
Date: Thu, 24 Jan 2008 22:19:12 -0800 (PST)
Message-ID: <33177750-96ef-4ae3-9d92-3a76e88108be@f10g2000hsf.googlegroups.com>

Thank you Steve Howard, I have found another one:

http://unix-cd.com/vc/www/16/2007-08/6738.html:

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 v.name = '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:

http://forums.oracle.com/forums/thread.jspa?forumID=61&threadID=458113
http://tonguc.wordpress.com/2006/12/28/new-dictionary-enhancements-with-10gr2-part-i/
http://tonguc.wordpress.com/2006/12/28/

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:

http://www.gplivna.eu/papers/oracle_trace.htm

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