Message-ID: <F001.00530CDD.20030115144848@fatcity.com>
Date: Wed, 15 Jan 2003 14:48:48 -0800
To: Multiple recipients of list ORACLE-L <ORACLE-L@fatcity.com>
Sender: root@fatcity.com
Reply-To: ORACLE-L@fatcity.com
Errors-To: ML-ERRORS@fatcity.com
From: "Reardon, Bruce (CALBBAY)" <Bruce.Reardon@comalco.riotinto.com.au>
Subject: RE: Tracing or not tracing, this is the question now.
Organization: Fat City Network Services, San Diego, California
Precedence: bulk
Mime-Version: 1.0
Content-Type: text/plain;	charset="iso-8859-1"
Content-Transfer-Encoding: 8bit

Mladen,

Form 2 previous list postings:


"
-----Original Message-----
Sent: Wednesday, 30 January 2002 5:51 AM
To: Multiple recipients of list ORACLE-L


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 
"

OR

It would seem this approach will only work for the current session
"
-----Original Message-----
[mailto:DBarbour@austin.isd.tenet.edu]
Sent: Friday, 6 July 2001 10:11 AM
To: Multiple recipients of list ORACLE-L


Hi Walt -

Kinda getting into this myself recently.  I found a reference to an
undocumented dbms package on Metalink today called: dbms_system.read_ev

I'm playing around with their test scripts to see what I can do with this
in conjunction with v$session and tracing and auditing.  Check out notes
1020308.6 and 28446.1

Hope this helps.

David A. Barbour
Oracle DBA, OCP
AISD
512-414-1002
"

-----Original Message-----
Sent: Thursday, 16 January 2003 8:41 AM

I'm looking for a way of checking which sessions on the database have trace turned on.
I can set event in another session, but I'd like to know whether the event (10046 in this case) 
is set in some sessions. 
Mladen Gogala
Oracle DBA
Oxford Health Plans
www.oxhp.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Reardon, Bruce (CALBBAY)
  INET: Bruce.Reardon@comalco.riotinto.com.au

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru@fatcity.com (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).