Script:who ' s using a database link? (Find out who is using Dblink)

Every once in awhile it was useful to find out which sessions was using a database link in an Oracle database. It's one of those things that your may isn't need very often, but if you do need it, it's usually rather important.

Yong Huang includes this script on he website, and notes that Mark further attributed authorship in Metalink Forum thread 524821.994. But the note is no longer available.

Here's the script, complete with comments.

-for 9I and below

--who are querying via Dblink?--courtesy of Tom Kyte, via Mark bobak--This script can being used at both ends of the Databa Se link--to match up which session on the remote database started--The local transaction--the GTXID would match for Thos E sessions--just run the script on both Databasesselect/*+ 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,10) "Status" , substr (w.event,1,10) "Waiting" from  x$k2gte G, X$ktcxb T, X$ksuse S, v$session_wait W, v$session s2where  G. K2GTDXCB =t.ktcxbxbaand   g.k2gtdses=t.ktcxbsesand  s.addr=g.k2gtdsesand  W.sid=s.indxand s2.sid = W.sid

-for 10g and above

SELECT/*+ ORDERED *      /SUBSTR (S.KSUSEMNM, 1, 10) | | '-' | | SUBSTR (S.ksusepid, 1,)          "ORIGIN",       SUBSTR (G.k2gtitid_ora, 1, +) "Gtxid",       SUBSTR (S.indx, 1, 4) | | '. ' | | SUBSTR (S.ksuseser, 1, 5) "Lsession",       S2.username, SUBSTR (DECODE (Bitand (Ksuseidl       ),             1, ' ACTIVE ',             0, DECODE (Bitand (KSUSEFLG, 4096), 0, ' INACTIVE ', ' CACHED '),             2, ' sniped ',             3, ' sniped ',             ' Killed '),          1, "          Status",       SUBSTR (s2.event, 1,) "Waiting" from  x$k2gte g,       X$KTCXB t ,       x$ksuse s,       v$session s2 WHERE     g.k2gtdxcb = t.ktcxbxba and       g.k2gtdses = t.ktcxbses and       s.addr = G.k2gtdses and       s2.sid = s.indx;

If you want to close a link, issue the following statement, where Linkname refers to the name of the link:

Sql> commit or rollback; Sql> alter session close database link linkname; Session altered.

References Jared ' s article

Script:who ' s using a database link? (Find out who is using Dblink)