Oracle Database 12 -- Match Local and Remote Sessions Connected via DB Links

Problem

You are connecting to a remote database using a db link. You need to identify and match local and remote sessions, that is, for each local SID, you need a corresponding SID on the remote database

 

Solution

You may use the below query to run on each database, the local and the remote. You will need the SYSDBA role to be able to execute the query:

select s.sid, s.serial#, s.username, s.machine, g.K2GTITID_ORA
from  x$k2gte g -- distributed transactions
    , x$ktcxb t -- all transactions
    , v$session s
where  g.K2GTDXCB =t.ktcxbxba -- transaction addr
and   g.K2GTDSES=t.ktcxbses -- session addr
and  s.saddr=g.K2GTDSES

 

Using the value of column g.K2GTITID_ORA you will be able to match the sessions. 

 

For example:

Output on the local database

:

 

       SID    SERIAL# USERNAME             MACHINE         K2GTITID_ORA            
---------- ---------- -------------------- --------------- -------------------------
         1         55 SYSTEM               client_host     ORCL2.567518a3.2.4.1433 
        33        303 SYSTEM               client_host     ORCL2.567518a3.6.24.1626
        28        221 SYSTEM               client_host     ORCL2.567518a3.4.8.1251   

 

The output on the remote database:

 

       SID    SERIAL# USERNAME             MACHINE         K2GTITID_ORA            
---------- ---------- -------------------- --------------- -------------------------
        33      10993 ORCL2_DBLINK_USER    database_host   ORCL2.567518a3.2.4.1433 
        30      65265 ORCL2_DBLINK_USER    database_host   ORCL2.567518a3.6.24.1626
        37      19605 ORCL2_DBLINK_USER    database_host   ORCL2.567518a3.4.8.1251