Here you go...
1) Find the OSPID for the suspected user (other than pmon, smon and their
famiy)
select s.username, p.spid
from v$session s, v$process p
where s.paddr = p.addr;
2) Use oradebug to connect to that spid (here I have 26073, and the session
was tracing event 10046)
SVRMGR> oradebug setospid 26073
Oracle pid: 11, Unix process pid: 26073, image: oracle@myservername (TNS
V1-V3)
3) Dump the events for the connected spid...
SVRMGR> oradebug dump events 1
Statement processed.
4) Look in the trace file in the udump directory..
*** 2002-01-29 09:58:55.847
Dump event group for level SESSION
TC Addr Evt#(b10) Action TR Addr Arm Life
400E1B68 10046 1 400e1ba8 0 0
TR Name TR level TR address TR arm TR life
TR type
CONTEXT 8 ffffffff -1 2
0
^^^^^^^^^^^^^^^^^^^ This session is tracing.... event 10046 context at level
8
5) Dump it again (from 3)...
6) Look in the trace file again....
*** 2002-01-29 10:01:57.316
WAIT #1: nam='SQL*Net message from client' ela= 34381 p1=1650815232 p2=1
p3=0
=====================
PARSING IN CURSOR #1 len=56 dep=0 uid=80 oct=42 lid=80 tim=2447763925
hv=3475487367 ad='a0de14e0'
alter session set events '10046 trace name context off'
END OF STMT
PARSE #1:c=0,e=2,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=2447763926
EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=2447763927
-- Oops! The the smart A$$ turned off the trace, But got caught!!!
Following is what you see in the trace file when the session is not
tracing.....
Dump event group for level SESSION
TC Addr Evt#(b10) Action TR Addr Arm Life
~
I thank Ross for his nifty hints and his time last night, when I was having
a 'brain fart' while testing this...
I learned something new from him... :)
Cheers!
- Kirti
-----Original Message-----
Sent: Tuesday, January 29, 2002 11:06 AM
To: Multiple recipients of list ORACLE-L
Send them doggies home, KD, it's a done deal.
;-)
-----Original Message-----
You may turn the trace on or off that way, but how do you interrogate it to
find its event settings?
- Kirti
-----Original Message-----
Sent: Monday, January 28, 2002 6:42 PM
To: 'Deshpande, Kirti'; [EMAIL PROTECTED]
ORADEBUG SETORAPID, would make "current session" whatever you like, no?
-----Original Message-----
>From what I know, there is no way to find if a session has turned on the
tracing unless it is your own session. DBMS_SYSTEM.read_ev can you tell you
if the current session is tracing or not, but it can not interrogate other
sessions.
The only way I know is to check the trace files generated in the udump
directory.. but it is not fool-proof.
And since you can not find out who is tracing at the moment, you can not
stop it..
Ross, I sure would like to learn the trick. Can you please post it?? :)
Thanks.
- Kirti
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Deshpande, Kirti
INET: [EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Deshpande, Kirti
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
