How to find remote session executing over a database link
January 24, 2017 Leave a comment
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" , substr(decode(bitand(ksuseidl,11),1,'ACTIVE',0, decode(bitand(ksuseflg,4096),0,'INACTIVE','CACHED'), 2,'SNIPED',3,'SNIPED', 'KILLED'),1,1) "S", substr(event,1,10) "WAITING" from x$k2gte g, x$ktcxb t, x$ksuse s, v$session_wait w where g.K2GTDXCB =t.ktcxbxba and g.K2GTDSES=t.ktcxbses and s.addr=g.K2GTDSES and w.sid=s.indx;
GTXID is the same on both databases.
################################### Sample output ###################################
##Destination
3 LBREPDB01-51715 LBREP.aa2c0b4f.94.11.4694801 5447.62951 I SQL*Net me
##Source
2 LB\MARIAMI-41196:4058 LBREP.aa2c0b4f.94.11.4694801 87.36231 I SQL*Net me
More Details:
SID – 87 SERIAL – 36231