分别在local instance的两个session中执行：
--在一个session：
select * from t1@ora9i for update;
--在另一个session:
delete from t2@ora9i where rownum<=3;
如何查找正在进行的分布式事务，可以用下面的几种方法：
（1）语句如下：
select username, osuser, status, sid, serial#, machine, process, terminal, program
from v$session
where saddr in (select k2gtdses from sys.x$k2gte );
显式结果:
SQL> select username, osuser, status, sid, serial#, machine, process, terminal, program
2 from v$session
3 where saddr in (select k2gtdses from sys.x$k2gte);
USERNAME OSUSER STATUS SID SERIAL# MACHINE PROCESS TERMINAL PROGRAM
------------------------------ ------------------------------ -------- ---------- ---------- ---------------------------------------------------------------- ------------ ------------------------------ ------------------------------------------------
SYS jijihe INACTIVE 141 14 CN-ORACLE\HE-PC 7876:9000 HE-PC plsqldev.exe
SYS jijihe INACTIVE 145 2 CN-ORACLE\HE-PC 7876:9000 HE-PC plsqldev.exe
SQL>
（2）语句如下：
[oracle10g@testdb tmp]$ cat chk_dx.sql
REM distri.sql
column origin format a13
column GTXID format a35
column LSESSION format a10
column s format a1
column waiting format a15
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.K2GTeXCB =t.ktcxbxba <= use this if running in Oracle7
where g.K2GTDXCB =t.ktcxbxba -- comment out if running in Oracle8 or later
and g.K2GTDSES=t.ktcxbses
and s.addr=g.K2GTDSES
and w.sid=s.indx;
REM distri_details.sql
set headin off
select /*+ ORDERED */
'----------------------------------------'||'
Curent Time : '|| substr(to_char(sysdate,'dd-Mon-YYYY HH24.MI.SS'),1,22) ||'
'||'GTXID='||substr(g.K2GTITID_EXT,1,10) ||'
'||'Ascii GTXID='||g.K2GTITID_ORA ||'
'||'Branch= '||g.K2GTIBID ||'
Client Process ID is '|| substr(s.ksusepid,1,10)||'
running in machine : '||substr(s.ksusemnm,1,80)||'
Local TX Id ='||substr(t.KXIDUSN||'.'||t.kXIDSLT||'.'||t.kXIDSQN,1,10) ||'
Local Session SID.SERIAL ='||substr(s.indx,1,4)||'.'|| s.ksuseser ||'
is : '||decode(bitand(ksuseidl,11),1,'ACTIVE',0,
decode(bitand(ksuseflg,4096),0,'INACTIVE','CACHED'),
2,'SNIPED',3,'SNIPED', 'KILLED') ||
' and '|| substr(STATE,1,9)||
' since '|| to_char(SECONDS_IN_WAIT,'9999')||' seconds' ||'
Wait Event is :'||'
'|| substr(event,1,30)||' '||p1text||'='||p1
||','||p2text||'='||p2
||','||p3text||'='||p3 ||'
Waited '||to_char(SEQ#,'99999')||' times '||'
Server for this session:' ||decode(s.ksspatyp,1,'Dedicated Server',
2,'Shared Server',3,
'PSE','None') "Server"
from x$k2gte g, x$ktcxb t, x$ksuse s, v$session_wait w
-- where g.K2GTeXCB =t.ktcxbxba <= use this if running Oracle7
where g.K2GTDXCB =t.ktcxbxba -- comment out if running Oracle8 or later
and g.K2GTDSES=t.ktcxbses
and s.addr=g.K2GTDSES
and w.sid=s.indx;
set headin on
-- end script
显示结果：
SQL> @chk_dx.sql
ORIGIN GTXID LSESSION S WAITING
------------- ----------------------------------- ---------- - ---------------
CN-ORACLE\-78 ORA10G.7859469f.7.29.136 141.14 I SQL*Net me
76:9000
CN-ORACLE\-78 ORA10G.7859469f.6.23.128 145.2 I SQL*Net me
76:9000
----------------------------------------
Curent Time : 16-Oct-2015 16.30.39
GTXID=4F52413130
Ascii GTXID=ORA10G.7859469f.7.29.136
Branch= 0000
Client Process ID is 7876:9000
running in machine : CN-ORACLE\HE-PC
Local TX Id =7.29.136
Local Session SID.SERIAL =141.14
is : INACTIVE and WAITING since 354 seconds
Wait Event is :
SQL*Net message from client driver id=1413697536,#bytes=1,=0
Waited 60 times
Server for this session:Dedicated Server
----------------------------------------
Curent Time : 16-Oct-2015 16.30.39
GTXID=4F52413130
Ascii GTXID=ORA10G.7859469f.6.23.128
Branch= 0000
Client Process ID is 7876:9000
running in machine : CN-ORACLE\HE-PC
Local TX Id =6.23.128
Local Session SID.SERIAL =145.2
is : INACTIVE and WAITING since 492 seconds
Wait Event is :
SQL*Net message from client driver id=1413697536,#bytes=1,=0
Waited 216 times
Server for this session:Dedicated Server
SQL>
（3）对于XA 事务，我们也能这样查：
语句如下：
[oracle10g@testdb tmp]$ cat get_xa_stat.sql
set pagesize 1000
prompt +++ In memory transaction +++
select /*+ ORDERED */
''||'
Curent Time : '|| substr(to_char(sysdate,' HH24.MI.SS'),1,22) ||'
'||'TX start_time: '||t.KTCXBSTM||'
'||'FORMATID: '||g.K2GTIFMT ||'
'||'GTXID: '||g.K2GTITID_EXT ||'
'||'Branch: '||g.K2GTIBID ||'
Local_Tran_Id ='||substr(t.KXIDUSN||'.'||t.kXIDSLT||'.'||t.kXIDSQN,1,15)||'
'||'KTUXESTA='|| x.KTUXESTA ||'
'||'KTUXEDFL='|| x.KTUXECFL ||'
Lock_Info: ID1: ' || ((t.kXIDUSN*64*1024)+ t.kXIDSLT)
||' ID2: '|| t.kXIDSQN
XA_transaction_INFO
from x$k2gte g, x$ktcxb t, x$ktuxe x
where g.K2GTDXCB =t.ktcxbxba and
x.KTUXEUSN = t.KXIDUSN(+) and
x.KTUXESLT = t.kXIDSLT(+) and
x.KTUXESQN =t.kXIDSQN(+);
prompt +++ Timed out, prepared XA transactions +++
select global_tran_fmt, global_foreign_id, branch_id,state,
tran.local_tran_id
from sys.pending_trans$ tran, sys.pending_sessions$ sess
where tran.local_tran_id = sess.local_tran_id
and tran.state = 'prepared'
and dbms_utility.is_bit_set(tran.session_vector, sess.session_id)=1;
[oracle10g@testdb tmp]$
显示结果：
SQL> @get_xa_stat
+++ In memory transaction +++
XA_TRANSACTION_INFO
--------------------------------------------------------------------------------
Curent Time : 16.38.13
TX start_time: 10/16/15 16:24:39
FORMATID: 306206
GTXID: 4F52413130472E37383539343639662E372E32392E313336
Branch: 0000
Local_Tran_Id =7.29.136
KTUXESTA=ACTIVE
KTUXEDFL=NONE
Lock_Info: ID1: 458781 ID2: 136
Curent Time : 16.38.13
TX start_time: 10/16/15 16:22:24
FORMATID: 306206
GTXID: 4F52413130472E37383539343639662E362E32332E313238
Branch: 0000
Local_Tran_Id =6.23.128
KTUXESTA=ACTIVE
KTUXEDFL=NONE
Lock_Info: ID1: 393239 ID2: 128
+++ Timed out, prepared XA transactions +++
no rows selected
SQL>
参考：
How to identify a session started by a remote distributed transaction? (Doc ID 332326.1)
Script to show Active Distributed Transactions (Doc ID 104420.1)
Solving locking problems in a XA environment (Doc ID 1248848.1)