RE: Finding open database links database wide

From: Reardon, Bruce (CALBBAY) <Bruce.Reardon_at_comalco.riotinto.com.au>
Date: Thu, 17 Feb 2005 12:11:53 +1100
Message-ID: <E10A27083F21674ABFAF6CD3D0BAC2F9017C0C@calbbsv025.cal.riotinto.org>

This is a very nice implementation of the script Mark had provided to = http://www.jlcomp.demon.co.uk/faq/find_dist.html. Unfortunately neither this nor any others I have seen will detect who = has a link open (ie not issued a close link) but no transaction current = (ie have done a commit/roll).
This can be seen for your current session from v$dblink. May be time to start trying to write it myself (or maybe it can't be = done).
Thanks,
Bruce Reardon.

-----Original Message-----

From: Jared Still [mailto:jkstill_at_gmail.com] Sent: Tuesday, 15 February 2005 5:59 AM
Subject: Re: Finding open database links database wide

• who_dblink.sql
• who is querying via dblink?
• Courtesy of Mark Bobak

Select /*+ ORDERED */

substr(s.ksusemnm,1,10)||'-'|| substr(s.ksusepid,1,10)      "ORIGIN",
substr(g.K2GTITID_ORA,1,35) "GTXID",
substr(s.indx,1,4)||'.'|| substr(s.ksuseser,1,5) "LSESSION" ,

s2.username,
substr(

        decode(bitand(ksuseidl,11),
                1,'ACTIVE',
                0, decode( bitand(ksuseflg,4096) , =
0,'INACTIVE','CACHED'),
                2,'SNIPED',
                3,'SNIPED',
                'KILLED'
        ),1,1

) "S",
substr(event,1,10) "WAITING"
from x$k2gte g, x$ktcxb t, x$ksuse s, v$session_wait w, v$session s2 where g.K2GTDXCB =3Dt.ktcxbxba
and g.K2GTDSES=3Dt.ktcxbses

and  s.addr=3Dg.K2GTDSES
and  w.sid=3Ds.indx
and s2.sid =3D w.sid

/

NOTICE
This e-mail and any attachments are private and confidential and may = contain privileged information. If you are not an authorised recipient, = the copying or distribution of this e-mail and any attachments is = prohibited and you must not read, print or act in reliance on this = e-mail or attachments.
This notice should not be removed.
--

http://www.freelists.org/webpage/oracle-l Received on Wed Feb 16 2005 - 20:14:59 CST