RE: Finding open database links database wide
- From: "Mercadante, Thomas F" <thomas.mercadante@xxxxxxxxxxxxxxxxx>
- To: "'jkstill@xxxxxxxxx'" <jkstill@xxxxxxxxx>, DGoulet@xxxxxxxx
- Date: Mon, 14 Feb 2005 13:52:12 -0500
Isn't there one more join that is missing?
"and s.indx = w.sid"
There is no join between the v$session_wait table and anything else. This
original query returns too many rows for me.
-----Original Message-----
From: Jared Still [mailto:jkstill@xxxxxxxxx]
Sent: Monday, February 14, 2005 1:47 PM
To: DGoulet@xxxxxxxx
Cc: scott.hutchinson@xxxxxxxxxxxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx;
Bruce.Reardon@xxxxxxxxxxxxxxxxxxxxxxx
Subject: Re: Finding open database links database wide
Here's a slightly modified version of a script from Mark Bobak:
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
--
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
