Hoje um amigo me pediu ajuda para conseguir identificar a origem das consultas que estavam sendo executadas via dblink em um determinado database, abaixo seguem as consultas que utilizei para identificar as conexões remotas via dblink:
1 – Verificando as consultas por sql_id:
col origin for a30
col "GTXID" for a30
col lsession for a10
col username for a20
col waiting for a50
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,
DECODE (BITAND (ksuseidl, 11),
1, 'ACTIVE',
0, DECODE (BITAND (ksuseflg, 4096), 0, 'INACTIVE', 'CACHED'),
2, 'SNIPED',
3, 'SNIPED',
'KILLED')
"State",
SUBSTR (w.event, 1, 30) "WAITING"
FROM x$k2gte g,
x$ktcxb t,
x$ksuse s,
gv$session_wait w,
gv$session s2
WHERE g.K2GTDXCB = t.ktcxbxba
AND g.K2GTDSES = t.ktcxbses
AND s.addr = g.K2GTDSES
AND w.sid = s.indx
and s2.sql_id='sql_id' --sql_id sql
AND s2.sid = w.sid;2 – Identificando todas as consultas:
col origin for a30
col "GTXID" for a30
col lsession for a10
col username for a20
col waiting for a50
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,
decode(bitand(ksuseidl,11),
1,'ACTIVE',
0, decode( bitand(ksuseflg,4096) , 0,'INACTIVE','CACHED'),
2,'SNIPED',
3,'SNIPED',
'KILLED'
) "State",
substr(w.event,1,30) "WAITING"
from x$k2gte g, x$ktcxb t, x$ksuse s, gv$session_wait w, gv$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;MOS de referência:
Script to show Active Distributed Transactions (Doc ID 104420.1)