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不同.