Oracle 数据字典之x$k2gte

通过查询x$k2gte（[K]ernel [2]-phase commit [G]lobal [T]ransaction [E]ntry）视图可以知道本数据库和远程数据库通过dblink发起的sid，serial#，从而可以进一步获得一系列相关信息。

引用

SQL> desc x$k2gte
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
ADDR                                               RAW(4)
INDX                                               NUMBER
INST_ID                                            NUMBER
K2GTIFMT                                           NUMBER
K2GTITLN                                           NUMBER
K2GTIBLN                                           NUMBER
K2GTITID_ORA                                       VARCHAR2(64)
K2GTITID_EXT                                       RAW(64)
K2GTIBID                                           RAW(64)
K2GTECNT                                           NUMBER
K2GTDSES                                           RAW(4)
K2GTDBRN                                           RAW(4)
K2GTDXCB                                           RAW(4)
K2GTERCT                                           NUMBER
K2GTDPCT                                           NUMBER
K2GTDFLG                                           NUMBER
K2GTETYP                                           NUMBER

x$k2gte.K2GTITID_ORA=global transaction
x$k2gte.k2gtdses = v$session.saddr
x$k2gte.k2gtdxcb = v$transcation.addr
以下脚本可以参考

引用

-- 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

下面我们利用这个脚本进行些测试
本数据库执行

引用

[oracle@mcrac1 admin]$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Apr 3 11:37:14 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> create database link xe_dblink connect to testdblink identified by testdblink using 'xe';

Database link created.

SQL>  select sysdate from dual@xe_dblink;

SYSDATE
---------
03-APR-10
SQL> @who_dblink

ORIGIN
-----------------------------------------
GTXID
----------------------------------------------------------------------
LSESSION            USERNAME                       S  WAITING
------------------- ------------------------------ -- --------------------
mcrac1-24800
DBRAC.REGRESS.RDBMS.DEV.US.ORACLE.C
134.28115           SYS                            A  SQL*Net me

SQL> select SID,SERIAL#,paddr from v$session where PROCESS='24800';

       SID    SERIAL# PADDR
---------- ---------- --------
       134      28115 39E1F110

SQL> select pid,spid,username,SERIAL# from v$process where addr=(select  paddr from v$session where PROCESS=24800);

       PID SPID         USERNAME      SERIAL#
---------- ------------ ---------- ----------
        26 26101        oracle             20

SQL> !ps -ef|grep 26101
oracle   26101 24800  0 12:26 ?        00:00:00 oracledbrac1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

同样在远程数据库执行

引用

SQL> @who_dblink

ORIGIN
--------------------------------------------------------------------------------
GTXID
--------------------------------------------------------------------------------
LSESSION                              USERNAME                       S
------------------------------------- ------------------------------ ----
WAITING
----------------------------------------
mcrac1-

引用

26101

DBRAC.REGRESS.RDBMS.DEV.US.ORACLE.C
104.816                               TESTDBLINK                     I
SQL*Net me

SQL> select SID,SERIAL#,paddr from v$session where PROCESS='26101';

       SID    SERIAL# PADDR
---------- ---------- ----------------
       104        816 000000009D65DD20

SQL> select pid,spid,username,SERIAL# from v$process where addr=(select  paddr from v$session where PROCESS='26101');

       PID SPID         USERNAME           SERIAL#
---------- ------------ --------------- ----------
        17 20901        ora10g                  34

SQL> !ps -ef|grep 20901
ora10g   20901     1  0 12:26 ?        00:00:00 oraclexe (LOCAL=NO)