RE: Finding open database links database wide

From: John Kanagaraj <john.kanagaraj_at_hds.com>
Date: Mon, 14 Feb 2005 10:25:44 -0800
Message-ID: <960A9B249C99994AA3C7FCA016A0DA89CB792F@ussccem08.corp.hds.com>

>Well, that's one heck of a neat trick, except it does not tell
>you which
>user has which link open. Is there a way to catch that??=20

Dick, The follow SQL will help.

column ORIGIN format a39
column GTXID format a39 heading "GLOBAL TXN ID" column LSESSION format a10 heading "SID,SER#" column WAITING format a55 heading "WAITING FOR EVENT" column STATUS format a10 heading "STATUS"

Select /*+ ORDERED */

substr(s.ksusemnm,1,23)||'-'|| substr(s.ksusepid,1,15)      "ORIGIN",
substr(g.K2GTITID_ORA,1,35) "GTXID",
substr(s.indx,1,4)||','|| substr(s.ksuseser,1,5) "LSESSION" ,
substr(decode(bitand(ksuseidl,11),1,'ACTIVE',0,
decode(bitand(ksuseflg,4096),0,'INACTIVE','CACHED'),

2,'SNIPED',3,'SNIPED', 'KILLED'),1,10) "STATUS", event "WAITING"
  from sys.x$k2gte g, sys.x$ktcxb t, sys.x$ksuse s, v$session_wait w where g.K2GTDXCB =t.ktcxbxba
   and g.K2GTDSES=t.ktcxbses
   and s.addr=g.K2GTDSES

Hth,
John Kanagaraj <><
DB Soft Inc
Phone: 408-970-7002 (W)

http://tahiti.oracle.com - Manuals for DBAs (English only) http://www.bibleserver.com - Manual for Life (in English, Deutsch, French, Italian, Spanish, Portugese, Turkish,...)

The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -- http://www.freelists.org/webpage/oracle-l

Received on Mon Feb 14 2005 - 13:28:52 CST