How to find remote session executing over a database link

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: