RE: Finding open database links database wide

From: Goulet, Dick <DGoulet_at_vicr.com>
Date: Mon, 14 Feb 2005 13:42:55 -0500
Message-ID: <4001DEAF7DF9BD498B58B45051FBEA65022DFC75@25exch1.vicorpower.vicr.com>

John,

        Thanks. At least you got it right, I boboed. Damn it.=20

-----Original Message-----
From: John Kanagaraj [mailto:john.kanagaraj_at_hds.com]=20 Sent: Monday, February 14, 2005 1:26 PM
To: oracle-l_at_freelists.org
Subject: RE: Finding open database links database wide

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

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 =3Dt.ktcxbxba
   and g.K2GTDSES=3Dt.ktcxbses
   and s.addr=3Dg.K2GTDSES
 =20
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 -- http://www.freelists.org/webpage/oracle-l

Received on Mon Feb 14 2005 - 13:46:23 CST