经常有这样的需求，想看一看你的服务器全局事务的状态，通过Dblink连入或连出的链接在本机及远端机器对应的sid,serial#,spid都是多少？如果你也有这样的语句，请看看下面的SQL。
SELECT /*+ ORDERED */
S.KSUSEMNM "ORIGIN",
S.KSUUDLNA USERNAME,
S.KSUSEPNM,
S.INDX SID,
S.KSUSESER SERIAL#,
P.KSUPRPID SPID,
G.K2GTITID_ORA "GTXID",
DECODE(BITAND(KSUSEIDL, 11),
1,
'ACTIVE',
0,
DECODE(BITAND(KSUSEFLG, 4096), 0, 'INACTIVE', 'CACHED'),
2,
'SNIPED',
3,
'SNIPED',
'KILLED') "STATUS",
EVENT "WAITING"
FROM X$K2GTE G, X$KTCXB T, X$KSUSE S, V$SESSION_WAIT W, X$KSUPR P
WHERE G.K2GTDXCB = T.KTCXBXBA
AND G.K2GTDSES = T.KTCXBSES
AND S.ADDR = G.K2GTDSES
AND W.SID = S.INDX
AND S.KSUSEPRO = P.ADDR
-- AND SUBSTR(G.K2GTITID_ORA, 1, 35) = 'XREP.1e55ca62.77.4.729'
-- AND W.EVENT='SQL*NET MESSAGE FROM DBLINK'
ORDER BY WAITING, STATUS
SELECT /*+ ORDERED */
S.KSUSEMNM "ORIGIN",
S.KSUUDLNA USERNAME,
S.KSUSEPNM,
S.INDX SID,
S.KSUSESER SERIAL#,
P.KSUPRPID SPID,
G.K2GTITID_ORA "GTXID",
DECODE(BITAND(KSUSEIDL, 11),
1,
'ACTIVE',
0,
DECODE(BITAND(KSUSEFLG, 4096), 0, 'INACTIVE', 'CACHED'),
2,
'SNIPED',
3,
'SNIPED',
'KILLED') "STATUS",
EVENT "WAITING"
FROM X$K2GTE G, X$KTCXB T, X$KSUSE S, V$SESSION_WAIT W, X$KSUPR P
WHERE G.K2GTDXCB = T.KTCXBXBA
AND G.K2GTDSES = T.KTCXBSES
AND S.ADDR = G.K2GTDSES
AND W.SID = S.INDX
AND S.KSUSEPRO = P.ADDR
-- AND SUBSTR(G.K2GTITID_ORA, 1, 35) = 'XREP.1e55ca62.77.4.729'
-- AND W.EVENT='SQL*NET MESSAGE FROM DBLINK'
ORDER BY WAITING, STATUS
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/9134/viewspace-166506/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
