Understand XID in Database View and Trace File

我们知道Transaction ID(XID)的组成是:Undo segment No. + Slot No. + Sequence No.

但是在数据字典中查询出来的XID和跟踪文件中的XID表现形式却有所不同,跟踪文件中的更加直观,而数据字典中的则需要做简单转换。

比如有一个事务,在数据字典中对应的Undo Block是:

SQL> select UBAFIL,UBABLK from v$transaction;
 
    UBAFIL     UBABLK
---------- ----------
         3       1854

获取block dump跟踪文件。

SQL> alter system dump datafile 3 block 1854; 
 
System altered 

SQL> @gettrace;
 
TRACEFILE
--------------------------------------------------------------------------------
d:\oracle\diag\rdbms\orcl11g\orcl11g\trace\orcl11g_ora_1380.trc

检查orcl11g_ora_1380.trc中的相应内容可以看到xid的格式是:xid: 0x0006.002.000004d1,为16进制。

其中0006 = 10进制的6
002 = 10进制的2
000004d1 = 10进制的1233

因此我们可以知道对于这个事务,Undo segment No是6,Slot No是2,Sequence No是1233。

从v$transaction视图中也可以验证。

SQL> select XIDUSN,XIDSLOT,XIDSQN from v$transaction;
 
    XIDUSN    XIDSLOT     XIDSQN
---------- ---------- ----------
         6          2       1233

但是查询v$transaction视图中的XID字段,却得到看上去不太一样的数值。

SQL> select XIDUSN,XIDSLOT,XIDSQN,XID from v$transaction;
 
    XIDUSN    XIDSLOT     XIDSQN XID
---------- ---------- ---------- ----------------
         6          2       1233 06000200D1040000

在这里我们需要做一下转换。
1. 06000200D1040000 = 0600.0200.D1040000
2. 再做高低位互换,0600 => 0006,0200 => 0002,D1040000 => 000004D1
3. 获得XID = 0006.0002.000004D1,这时跟block dump中记录的xid就完全一样了。

via Comment@babyblue
通过SQL来获取就是:

select to_number(substr(xid, 3, 2) || substr(xid, 1, 2), 'xxxxxxxxxxxx') xidusn,
       to_number(substr(xid, 7, 2) || substr(xid, 5, 2), 'xxxxxxxxxxxx') xidslot,
       to_number(substr(xid, 15, 2) || substr(xid, 13, 2) ||
                 substr(xid, 11, 2) || substr(xid, 9, 2),
                 'xxxxxxxxxxxx') xidsqn
  from (select '06000200D1040000' xid from dual);

4 thoughts on “Understand XID in Database View and Trace File

  1. select
    to_number(substr(xid,3,2)||substr(xid,1,2),’xxxxxxxxxxxx’) xidusn,
    to_number(substr(xid,7,2)||substr(xid,5,2),’xxxxxxxxxxxx’) xidslot,
    to_number(substr(xid,15,2)||substr(xid,13,2)||substr(xid,11,2)||substr(xid,9,2),’xxxxxxxxxxxx’) xidsqn
    from (
    select ‘06000200D1040000’ xid from dual);

Leave a Reply

Your email address will not be published. Required fields are marked *