ITL中xid 和 uba的驗證！

ITL中xid 和 uba代表了什麼意思？

需要了解block的internal格式！下面透過一個試驗做了一些驗證！

SQL> truncate table t;

表被截斷。

SQL> desc t
名稱 是否為空? 型別
----------------------------------------- -------- --------------------------

ID NUMBER(38)
NAME VARCHAR2(10)

SQL> insert into t values(1,'a');

已建立 1 行。

SQL> select id ,name , rowid from t;

ID NAME ROWID
---------- ---------- ------------------
1 a AAADhJAAEAAAAAUAAA

SQL> select dbms_rowid.rowid_block_number(rowid) from t;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
20

SQL> alter system dump datafile 4 block 20;

系統已更改。
dump資訊如下：
--=================================================================================
Start dump data blocks tsn: 8 file#: 4 minblk 20 maxblk 20
buffer tsn: 8 rdba: 0x01000014 (4/20)
scn: 0x0000.0027fd5b seq: 0x03 flg: 0x00 tail: 0xfd5b0603
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x081D2200 to 0x081D4200
.............................
Block header dump: 0x01000014
Object id on Block? Y
seg/obj: 0x3849 csc: 0x00.27fd5b itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1000011 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000c.011.000000e6 0x00800554.00d6.28 ---- 1 fsc 0x0000.00000000
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

data_block_dump,data header at 0x81d2264
===============
tsiz: 0x1f98
hsiz: 0x14
pbl: 0x081d2264
bdba: 0x01000014
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f90
avsp=0x1f7b
tosp=0x1f7b
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x1f90
block_row_dump:
tab 0, row 0, @0x1f90
tl: 8 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 02
col 1: [ 1] 61
end_of_block_dump
End dump data blocks tsn: 8 file#: 4 minblk 20 maxblk 20

--=================================================================================
data block的dump資訊中ITL中的xid：0x000c.011.000000e6是由undo資訊組成的：xidusn.xidslot.xidsqn
驗證如下：
SQL> select to_number('000c','xxxxx') from dual;

TO_NUMBER('000C','XXXXX')
-------------------------
12

SQL> select to_number('011','xxxxx') from dual;

TO_NUMBER('011','XXXXX')
------------------------
17

SQL> select to_number('000000e6','xxxxxxxxx') from dual;

TO_NUMBER('000000E6','XXXXXXXXX')
---------------------------------
230

--=================================================================================
data block的dump資訊中ITL中的uba：0x00800554.00d6.28是由uba（undo block address）.UBASQN.UBAREC
undo block address是00800554，是一個16進位制數，可以透過如下函式轉換為UBAFIL和UBABLK
驗證如下：
--=================================================================================
SQL> select dbms_utility.data_block_address_file(to_number('00800554','xxxxxxx
x')) from dual;

DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(TO_NUMBER('00800554','XXXXXXXXXX'))
------------------------------------------------------------------------
2

SQL> select dbms_utility.data_block_address_block(to_number('00800554','xxxxxx
xx')) from dual;

DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(TO_NUMBER('00800554','XXXXXXXXXX'))
-------------------------------------------------------------------------
1364

SQL> select to_number('00d6','xxxxxxxxx') from dual;

TO_NUMBER('00D6','XXXXXXXXX')
-----------------------------
214
--=================================================================================
上面dump之後計算的資訊和v$transaction的資料完全一致
SQL> select xidusn,xidslot,xidsqn,ubafil,ubablk,ubasqn,ubarec from v$transaction

2 ;

XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC
---------- ---------- ---------- ---------- ---------- ---------- ----------
12 17 230 2 1364 214 40

SQL>