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.
联系客服