Oracle-L: RE: RE: Tracing or not tracing, this is the question now.

From: Stephane Faroult <sfaroult_at_oriolecorp.com>
Date: Thu, 16 Jan 2003 00:33:41 -0800
Message-ID: <F001.00530F8A.20030116003341@fatcity.com>

('binary' encoding is not supported, stored as-is)
I am not sure that this answers Mladen's question. A brute force solution, not necessarily very efficient and questionable, security-wise, would be to make user_dump_dest accessible through utl_file, check v$session and see for each session whether there is a matching .trc file - and in fact open it to be sure that it's not an old file which once was associated with an identical pid. One might probably build some dummy view with the object option over this ... Too bad I lack time ...

>----- Original Message -----
>From: "Reardon, Bruce (CALBBAY)"
><Bruce.Reardon_at_comalco.riotinto.com.au>
>To: Multiple recipients of list ORACLE-L
><ORACLE-L_at_fatcity.com>
>Sent: Wed, 15 Jan 2003 14:48:48
>
>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_at_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
>EXEC
>
>-- 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_at_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_at_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_at_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).
>#1:c=0,e=2,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=2
>447763926
>#1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=2
>447763927
>---------------------------------------------------
>------------------

Regards,

Stephane Faroult
Oriole

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroul
  INET: sfaroult_at_oriolecorp.com

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_at_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).

Received on Thu Jan 16 2003 - 02:33:41 CST