Script to show Active Distributed Transactions

来源：互联网发布：mac os x sierrad安装编辑：程序博客网时间：2024/06/14 08:51

该脚本可以用于显示活跃的分布式事务(Distributed Transactions from dblink)，可以协助诊断dblink远程事务：

REM distri.sqlcolumn origin format a13column GTXID format a35column LSESSION format a10column s format a1column waiting format a15Select /*+ 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 Oracle7where  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.sqlset headin offselect /*+ 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 Oracle7where  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

##转载自：

http://www.oracledatabase12g.com/archives/category/oracle/distributed-transactions

0 0