Re: Unable to do a 10046 trace on another users session
- From: <ryan.gaffuri@xxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx, <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 26 Apr 2004 7:10:55 -0400
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
