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
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
---------- ---------- -------------------- --------------- -------------------------
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
---------- ---------- -------------------- --------------- -------------------------
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
No comments:
Post a Comment