Script: Who’s using a database link?(找出谁在使用dblink) | ANBOB

Every once in awhile it is useful to find out which sessions are using a database link in an Oracle database. It’s one of those things that you may not need very often, but when you do need it, it is usually rather important.

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

Here’s the script, complete with comments.

— for 9I and below

-- who is querying via dblink?-- Courtesy of Tom Kyte, via Mark Bobak-- this script can be used at both ends of the database link-- to match up which session on the remote database started-- the local transaction-- the GTXID will match for those 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, 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 (s2.event, 1, 10) "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;

for example from ora1 dblink to ora2:

# on ora2-- run above sql ORIGIN                GTXID                               LSESSION   USERNAME                       Status   WAITING--------------------- ----------------------------------- ---------- ------------------------------ -------- ----------qdyyc1-5990           TBCSC.ANBOB.COM.3bf61471.74.        2240.26293 TBCS                           INACTIVE SQL*Net me# on ora1SQL> select s.indx sid,kSUSESER serial#,KSUSEMNM machine,KSUSEPNM prog,KSUSEPSI from x$ksuse s,x$k2gte g where s.addr=g.k2gtdses and g.k2gtitid_ora like 'TBCSC.ANBOB.COM.3bf61471.74%';       SID    SERIAL# MACHINE                                PROG                                             KSUSEPSI---------- ---------- -------------------------------------- ------------------------------------------------ -------------      2328       1419 qmwebc03                               JDBC Thin Client                                 c9rmk6qpu1t9k

Tips:
session sid 2328 on ora1 , dblink to ora2 and remote session sid is 2240.

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.