实验: Oracle事务(ITL)

1 建表,插入数据

SQL> create table test(id number, name varchar2(10));

Table created

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

1 row inserted

SQL> select id,

2 name,

3 rowid,

4 dbms_rowid.rowid_relative_fno(rowid) file#,

5 dbms_rowid.rowid_block_number(rowid) block#

6 from test;

ID NAME ROWID FILE# BLOCK#

---------- ---------- ------------------ ---------- ----------

1 a AAANLOAAEAAAAMAAAA 4 768

2 Dump块

SQL> alter system dump datafile 4 block 768;

System altered

从udump文件中Trace文件可以得信息:

=======================================================================================

……

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0x0008.012.0000020d 0x008000af.01cf.1d ---- 1 fsc 0x0000.00000000

0x02 0x000a.007.000001cd 0x0080035c.0148.0d --U- 1 fsc 0x0007.001a603f

……

=======================================================================================

ITL中的xid：0x0005.020.0000020e是由undo信息组成的：xidusn.xidslot.xidsqn

ITL中的uba：0x0080024d.0129.1f是由uba（undo block address）.UBASQN.UBAREC

undo block address是0080024d，是一个16进制数，可以通过如下函数转换为UBAFIL和UBABLK

3 分解xid

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

XIDUSN

----------

8

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

XIDSLOT

----------

18

SQL> select to_number('0000020d','xxxxxxxxx') xidsqn from dual;

XIDSQN

----------

525

4 分解uba

SQL> select dbms_utility.data_block_address_file(to_number('008000af','xxxxxxxx')) UBAFIL from dual;

UBAFIL

----------

2

SQL> select dbms_utility.data_block_address_block(to_number('008000af','xxxxxxxx')) UBABLK from dual;

UBABLK

----------

175

SQL> select to_number('01cf','xxxxxxxxx') UBASQN from dual;

UBASQN

----------

463

SQL> select to_number('1d','xxxxxxxxx') UBAREC from dual;

UBAREC

----------

29

5 dump信息和v$transaction比较

SQL> select xidusn, xidslot, xidsqn, ubafil, ubablk, ubasqn, ubarec from v$transaction;

XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC

---------- ---------- ---------- ---------- ---------- ---------- ----------

8 18 525 2 175 463 29

6 数据块锁定

在另一个会话中操作:

SQL> insert into test values(2, 'a');

1 row inserted

SQL> select id,

2 dbms_rowid.rowid_relative_fno(rowid) file#,

3 dbms_rowid.rowid_block_number(rowid) block#

4 from test;

ID FILE# BLOCK#

---------- ---------- ----------

2 4 767

第一个会话由于未提交, 而锁定了数据块768, 所以第二个会话新插入的数据在新的块767, 与之前的768不同.