Unfortunately for those of us charged with the care and feeding of the Oracle RDBMS, this information is not terribly easy to track down.
Some years ago when I first had need to determine which sessions were at each end of database link, I found a script that was supplied courtesy of Mark Bobak. When asked, Mark said he got the script from Tom Kyte. Yong Huang includes this script on his website, and notes that Mark further attributed authorship in Metalink Forum thread 524821.994. Yong has informed me that this note is no longer available. I have found the script in other locations as well, such Dan Morgan's website. So now you know the scripts provenance.
Here's the script, complete with comments. Following the script is an example of usage.
-- 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 databases
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,1
) "S",
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
Now let's take a look a the results of the script.
Logging on to DB1 as system, create a database link to db2 using the SCOTT account:
create database link scott_link connect to scott identified by "tiger" using 'db2';
Make sure it works:
system@db1 SQL> select sysdate from dual@scott_link;
SYSDATE
-------------------
03/05/2010 10:13:00
1 row selected.
Now logon to DB1 as sysdba and run who_dblink.sql:
sys@db1 SQL> @who_dblink
ORIGIN GTXID LSESSION USERNAME S WAITING
--------------------- ----------------------------------- ---------- ---------- - ----------
oraserver.-21901 DB1.d6d6d69e.3.16.7190 500.15059 SYSTEM I SQL*Net me
1 row selected.
Now do the same on DB2:
sys@db2 SQL> @who_dblink
ORIGIN GTXID LSESSION USERNAME S WAITING
--------------------- ----------------------------------- ---------- ---------- - ----------
ordevdb01.-21903 DB1.d6d6d69e.3.16.7190 138.28152 SCOTT I SQL*Net me
1 row selected.
How do you identify the session on the database where the database link connection was initiated?
Notice that the output from DB1 shows the PID in the ORIGIN column. In this case it is 21901.
Running the following SQL on DB1 we can identify the session from which the SYSTEM user initiated the database link connection:
select
b.username,
b.sid SID,
b.serial# SERIAL#,
b.osuser,
b.process
from v$session b,
v$process d,
v$sess_io e,
v$timer
where
b.sid = e.sid
and b.paddr = d.addr
and b.username is not null
-- added 0.0000001 to the division above to
-- avoid divide by zero errors
-- this is to show all sessions, whether they
-- have done IO or not
--and (e.consistent_Gets + e.block_Gets) > 0
-- uncomment to see only your own session
--and userenv('SESSIONID') = b.audsid
order by
b.username,
b.osuser,
b.serial#,
d.spid,
b.process,
b.status,
e.consistent_Gets,
e.block_Gets,
e.Physical_reads;
USERNAME SID SERIAL # OS USER PROCESS
---------- ------ -------- -------------------- ------------------------
SYS 507 12708 oracle 22917
SYSTEM 500 15059 oracle 21901
2 rows selected.The session created using the SCOTT account via the database link can also be seen using the same query on DB2 and looking for the session with a PID of 21903:
USERNAME SID SERIAL# OSUSER PROCESS
---------- ---- ------- ------- ------------
SCOTT 138 28152 oracle 21903
SYS 147 33860 oracle 22991
146 40204 oracle 24096
3 rows selected.
