Re: Unable to do a 10046 trace on another users session

From: <ryan.gaffuri_at_cox.net>
Date: Mon, 26 Apr 2004 7:10:55 -0400
Message-Id: <20040426111055.RWMB164.lakermmtao12.cox.net@smtp.east.cox.net>

by last line of the sql. do you mean just one sql statement? cary is right then you are dealing with many sessions. We are having this problem with connection pooling.

The best way I found to handle it is to:

1. Make a user that has all the privileges of the username you want to trace.
2. Create a logon trigger to enable tracing when that user logs in.
3. Capture the full path of the trace file to a table.
4. write a korn shell script to tkprof all the trace files and concatenate them together (haven't had a chance to do this yet).

There query is from ixora.com.au. Here is the trigger:

CREATE OR REPLACE TRIGGER trig_trace_logon AFTER LOGON ON DATABASE BEGIN
  IF USER = 'TRACE' THEN
    INSERT INTO MYUSER.TRACE_LOG
    SELECT d.value||'/'||lower(rtrim(i.instance, chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name,

           SYSDATE
      FROM


  ( select

           p.spid
      from
           sys.v_$mystat m,
           sys.v_$session s,
           sys.v_$process p
     where
           m.statistic# = 1 and
           s.sid = m.sid and
           p.addr = s.paddr

  ) p,
( select

           t.instance
      from
           sys.v_$thread  t,
           sys.v_$parameter  v
      where
           v.name = 'thread' and
      (
            v.value = 0 or
            t.thread# = to_number(v.value)
      )

   ) i,
( select

            value
      from
            sys.v_$parameter
     where
            name = 'user_dump_dest'

  ) d;
    DBMS_SUPPORT.START_TRACE( waits=>true, binds=>true );   END IF;
END;

---

Please see the official ORACLE-L FAQ: http://www.orafaq.com

---

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Mon Apr 26 2004 - 06:07:57 CDT