RE: Finding open database links database wide

From: Mercadante, Thomas F <thomas.mercadante_at_labor.state.ny.us>
Date: Mon, 14 Feb 2005 13:52:12 -0500
Message-ID: <C9995D8C5E0DDA4A8FF9D68EE666CE0702A97356@exchsen0a1ma>

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_at_gmail.com] Sent: Monday, February 14, 2005 1:47 PM
To: DGoulet_at_vicr.com
Cc: scott.hutchinson_at_interact-analysis.com; oracle-l_at_freelists.org; Bruce.Reardon_at_comalco.riotinto.com.au
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
--

http://www.freelists.org/webpage/oracle-l
--

http://www.freelists.org/webpage/oracle-l Received on Mon Feb 14 2005 - 13:55:06 CST