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 databases

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(w.event,1,10) "WAITING"
from  x$k2gte g, x$ktcxb t, x$ksuse s, v$session_wait w, v$session s2
where  g.K2GTDXCB =t.ktcxbxba
and   g.K2GTDSES=t.ktcxbses
and  s.addr=g.K2GTDSES
and  w.sid=s.indx
and 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 ora1
SQL> 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.