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

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

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 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;