分别在local instance的两个session中执行：
|
1 2 3 4 5 |
--在一个session： select * from t1@ora9i for update; --在另一个session: delete from t2@ora9i where rownum<=3; |
如何查找正在进行的分布式事务，可以用下面的几种方法：
（1）语句如下：
|
1 2 3 |
select username, osuser, status, sid, serial#, machine, process, terminal, program from v$session where saddr in (select k2gtdses from sys.x$k2gte ); |
显式结果:
|
1 2 3 4 5 6 7 8 9 10 |
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）语句如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 |
[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 |
显示结果：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
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 事务，我们也能这样查：
语句如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
[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]$ |
显示结果：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
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)