Transaction & Undo
本文尝试通过一个简单的测试来发现transaction和undo之间的关系。
Setup (创建测试环境）
---------------------------------------------
创建一张表, 插入一条记录
SQL> create tablespace tx_demo datafile 'F:\Ora_Data_File\tx_demo01.dbf' size 10m
2 autoextend on maxsize 20M
3 extent management local uniform size 64K;
Tablespace created.
SQL> create user tx_demo identified by demo
2 default tablespace tx_demo
3 quota unlimited on tx_demo;
User created.
SQL> grant dba to tx_demo;
Grant succeeded.
SQL> conn tx_demo/demo@10.80.15.117/orcl
Connected.
SQL> create table test
2 ( id number(10), name varchar2(10));
Table created.
SQL> insert into test values(1, 'Frank');
1 row created.
SQL> commit;
Commit complete.
2 autoextend on maxsize 20M
3 extent management local uniform size 64K;
Tablespace created.
SQL> create user tx_demo identified by demo
2 default tablespace tx_demo
3 quota unlimited on tx_demo;
User created.
SQL> grant dba to tx_demo;
Grant succeeded.
SQL> conn tx_demo/demo@10.80.15.117/orcl
Connected.
SQL> create table test
2 ( id number(10), name varchar2(10));
Table created.
SQL> insert into test values(1, 'Frank');
1 row created.
SQL> commit;
Commit complete.
在没有active transaction发生的情况下分析data block的内容
---------------------------------------------
定位到data block的rdba...
SQL> select header_file, header_block, blocks, extents
2 from dba_segments
3 where owner='TX_DEMO'
4 and segment_name = 'TEST';
HEADER_FILE HEADER_BLOCK BLOCKS EXTENTS
----------- ------------ ---------- ----------
15 11 8 1
SQL> select file_id, block_id, blocks
2 from dba_extents
3 where segment_name='TEST'
4 and owner = 'TX_DEMO';
FILE_ID BLOCK_ID BLOCKS
---------- ---------- ----------
15 9 8
SQL> select dbms_rowid.rowid_relative_fno(rowid) fno,
2 dbms_rowid.rowid_block_number(rowid) block_no
3 from test;
FNO BLOCK_NO
---------- ----------
15 16
SQL> alter system dump datafile 15 block 16;
System altered.
SQL>
2 from dba_segments
3 where owner='TX_DEMO'
4 and segment_name = 'TEST';
HEADER_FILE HEADER_BLOCK BLOCKS EXTENTS
----------- ------------ ---------- ----------
15 11 8 1
SQL> select file_id, block_id, blocks
2 from dba_extents
3 where segment_name='TEST'
4 and owner = 'TX_DEMO';
FILE_ID BLOCK_ID BLOCKS
---------- ---------- ----------
15 9 8
SQL> select dbms_rowid.rowid_relative_fno(rowid) fno,
2 dbms_rowid.rowid_block_number(rowid) block_no
3 from test;
FNO BLOCK_NO
---------- ----------
15 16
SQL> alter system dump datafile 15 block 16;
System altered.
SQL>
注意到表TEST的第一个extent是从block 9开始的，而segment header是位于block 11. 表中的数据行却位于block 16. （注意： 看到有些文章提到说通过第二个查询得到的block是segment header, 而实际存储表中数据的block是接下来的block开始的，这显然与通过rowid得出的结论是不一样的）
节选出data block （16）的转出文件的片断...
Start dump data blocks tsn: 18 file#: 15 minblk 16 maxblk 16
(Data file number #15, Block number from 16 to 16)
buffer tsn: 18 rdba: 0x03c00010 (15/16)
scn: 0x0000.04023fde seq: 0x03 flg: 0x06 tail: 0x3fde0603
frmt: 0x02 chkval: 0xd687 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x08A78000 to 0x08A7A000
.....
.....
Block header dump: 0x03c00010
Object id on Block? Y
seg/obj: 0x568dd csc: 0x00.4023fda itc: 2 flg: E typ: 1 - DATA
buffer tsn: 18 rdba: 0x03c00010 (15/16)
scn: 0x0000.04023fde seq: 0x03 flg: 0x06 tail: 0x3fde0603
frmt: 0x02 chkval: 0xd687 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x08A78000 to 0x08A7A000
.....
.....
Block header dump: 0x03c00010
Object id on Block? Y
seg/obj: 0x568dd csc: 0x00.4023fda itc: 2 flg: E typ: 1 - DATA
(itc: 2 = Number of ITL slots)
brn: 0 bdba: 0x3c00009 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0001.000.000077b3 0x00800195.230a.19 --U- 1 fsc 0x0000.04023fde
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
brn: 0 bdba: 0x3c00009 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0001.000.000077b3 0x00800195.230a.19 --U- 1 fsc 0x0000.04023fde
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
ITL -- Interested Transaction List
Xid -- Transaction ID, composed of three parts...
(1) XIDUSN - Undo segment number
(2) XIDSLOT - Slot number
(3) XIDSQN - Sequence number(wrap)
Uba -- Undo Block Address, composed of three parts...
(1) UBAFIL, UBABLK - DBA (file and block number) of undo block
(2) UBASQN - Sequence number of undo block
(3) UBAREC - Record number in undo block
Flag -- Transaction flag
C--- = Committed
---- = Uncommitted
-B-- = The UBA (undo block address) contains undo for this ITL
--U- = Committed by fast commits & delayed block cleanout has not occurred.
---T = Transaction active at block cleanout SCN
C-U- = Block cleaned by delayed block cleanout, and rollback segment info is overwritten.
Lck -- Numbers of Row locked due to related transaction
Scn/Fsc -- System Change Number / Free Space Credit (if that data is deleted from the block)
data_block_dump,data header at 0x8a78064
===============
tsiz: 0x1f98 # Size of the block
hsiz: 0x14 # Size of the block header = 20 bytes
pbl: 0x08a78064 # Pointer to the block buffer holding the block
bdba: 0x03c00010 # Relative database block address
76543210
flag=--------
ntab=1 # Number of tables in block
nrow=1 # Number of rows
frre=-1 # First free row index entry, if -1 add 1
fsbo=0x14 # Free space begin offset
fseo=0x1f8c # Free space end offset
avsp=0x1f78 # Available block space
tosp=0x1f78 # Available space post commit
0xe:pti[0] nrow=1 offs=0 # Table info
0x12:pri[0] offs=0x1f8c # Row info, Rowid
block_row_dump: # Row data next
tab 0, row 0, @0x1f8c # Table 0, Row 0, Rowid
tl: 12 fb: --H-FL-- lb: 0x1 cc: 2 # lb:0x1 = This row is locked by ITL 0x1
col 0: [ 2] c1 02 # 1
col 1: [ 5] 46 72 61 6e 6b # FRANK
end_of_block_dump
End dump data blocks tsn: 18 file#: 15 minblk 16 maxblk 16
注意到这个data block的SCN (scn: 0x0000.04023fde ) 和 ITL 01的SCN (x0000.04023fde) 是一样的。
同时注意到ITL:0x01的flag(--U-) 表示是fast commit, 这个其实很好理解。刚才的操作是插入一条记录(1, 'Frank'), 然后commit, 数据块应该还存在于buffer cache之中，没有被写回data file中，因此可以直接在内存中就修改ITL的标识， 这就是所谓的fast block cleanout, （相应的，也有所谓的delayed block cleanout)。
对表进行Update操作，分析transaction 和 undo segment
---------------------------------------------
现在对表TEST进行update操作...
SQL> select * from test;
ID NAME
---------- ----------
1 Frank
SQL> update test set name = 'Fraud' where id =1;
1 row updated.
SQL> select ubafil, ubablk, ubasqn, ubarec, status, noundo, xid, start_scn
2 from v$transaction;
UBAFIL UBABLK UBASQN UBAREC STATUS NOU XID START_SCN
---------- ---------- ---------- ---------- ---------------- --- ---------------- ----------
2 2013 10565 5 ACTIVE NO 09000F0058B60000 67262618
SQL> update test set name = 'Test' where id = 1;
1 row updated.
SQL> select ubafil, ubablk, ubasqn, ubarec, status, noundo, xid, start_scn
2 from v$transaction;
UBAFIL UBABLK UBASQN UBAREC STATUS NOU XID START_SCN
---------- ---------- ---------- ---------- ---------------- --- ---------------- ----------
2 2013 10565 6 ACTIVE NO 09000F0058B60000 67262618
SQL> update test set name = 'Test2' where id = 1;
1 row updated.
SQL> select ubafil, ubablk, ubasqn, ubarec, status, noundo, xid, start_scn
2 from v$transaction;
UBAFIL UBABLK UBASQN UBAREC STATUS NOU XID START_SCN
---------- ---------- ---------- ---------- ---------------- --- ---------------- ----------
2 2013 10565 7 ACTIVE NO 09000F0058B60000 67262618
ID NAME
---------- ----------
1 Frank
SQL> update test set name = 'Fraud' where id =1;
1 row updated.
SQL> select ubafil, ubablk, ubasqn, ubarec, status, noundo, xid, start_scn
2 from v$transaction;
UBAFIL UBABLK UBASQN UBAREC STATUS NOU XID START_SCN
---------- ---------- ---------- ---------- ---------------- --- ---------------- ----------
2 2013 10565 5 ACTIVE NO 09000F0058B60000 67262618
SQL> update test set name = 'Test' where id = 1;
1 row updated.
SQL> select ubafil, ubablk, ubasqn, ubarec, status, noundo, xid, start_scn
2 from v$transaction;
UBAFIL UBABLK UBASQN UBAREC STATUS NOU XID START_SCN
---------- ---------- ---------- ---------- ---------------- --- ---------------- ----------
2 2013 10565 6 ACTIVE NO 09000F0058B60000 67262618
SQL> update test set name = 'Test2' where id = 1;
1 row updated.
SQL> select ubafil, ubablk, ubasqn, ubarec, status, noundo, xid, start_scn
2 from v$transaction;
UBAFIL UBABLK UBASQN UBAREC STATUS NOU XID START_SCN
---------- ---------- ---------- ---------- ---------------- --- ---------------- ----------
2 2013 10565 7 ACTIVE NO 09000F0058B60000 67262618
对表中的数据upate进行了三次, 都是对name进行更改（’Frank' -> 'Fraud' -> 'Test' -> 'Test2')。
从v$transaction的数据来看，这几次update操作让UBAREC （undo record）从5增加到了7， 而其他信息并没有更改。并且从v$transaction视图得出当前的transaction用到的undo block为(2, 2013)。
在有active transaction的情况下分析data block的内容
---------------------------------------------
在没有commit这些change之前，再转储data block (15, 16)，看看block发生了什么变化。
SQL> alter system dump datafile 15 block 16;
System altered.
System altered.
看看dump文件的内容....
Start dump data blocks tsn: 18 file#: 15 minblk 16 maxblk 16
buffer tsn: 18 rdba: 0x03c00010 (15/16)
scn: 0x0000.04025991 seq: 0x01 flg: 0x04 tail: 0x59910601
frmt: 0x02 chkval: 0xa1b7 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x08A78000 to 0x08A7A000
....
...
Block header dump: 0x03c00010
Object id on Block? Y
seg/obj: 0x568dd csc: 0x00.40258a1 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x3c00009 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0001.000.000077b3 0x00800195.230a.19 C--- 0 scn 0x0000.04023fde
0x02 0x0009.00f.0000b658 0x008007dd.2945.07 ---- 1 fsc 0x0000.00000000
data_block_dump,data header at 0x8a78064
===============
tsiz: 0x1f98
hsiz: 0x14
pbl: 0x08a78064
bdba: 0x03c00010
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f75
avsp=0x1f78
tosp=0x1f78
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x1f75
block_row_dump:
tab 0, row 0, @0x1f75
tl: 12 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 02
col 1: [ 5] 54 65 73 74 32
end_of_block_dump
End dump data blocks tsn: 18 file#: 15 minblk 16 maxblk 16
buffer tsn: 18 rdba: 0x03c00010 (15/16)
scn: 0x0000.04025991 seq: 0x01 flg: 0x04 tail: 0x59910601
frmt: 0x02 chkval: 0xa1b7 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x08A78000 to 0x08A7A000
....
...
Block header dump: 0x03c00010
Object id on Block? Y
seg/obj: 0x568dd csc: 0x00.40258a1 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x3c00009 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0001.000.000077b3 0x00800195.230a.19 C--- 0 scn 0x0000.04023fde
0x02 0x0009.00f.0000b658 0x008007dd.2945.07 ---- 1 fsc 0x0000.00000000
data_block_dump,data header at 0x8a78064
===============
tsiz: 0x1f98
hsiz: 0x14
pbl: 0x08a78064
bdba: 0x03c00010
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f75
avsp=0x1f78
tosp=0x1f78
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x1f75
block_row_dump:
tab 0, row 0, @0x1f75
tl: 12 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 02
col 1: [ 5] 54 65 73 74 32
end_of_block_dump
End dump data blocks tsn: 18 file#: 15 minblk 16 maxblk 16
这个时候注意到data block的scn发生了变化 （scn: 0x0000.04025991），跟ITL中的scn不一致了。从下面dump的undo block的内容可以看到，这个SCN和对应的undo block的scn是一致的。
注意ITL的变化，第一个slot的itl的flag变成了C(committed)了，而第二个itl的flag表示transaction尚未commit. 从表中的数据行的标志位lb:0x2可以看出现在改行指向itl第二个slot。itl 0x2中的内容和从v$transaction查询出来的数据是一致的。
查看Undo Segment Header的dump info.
---------------------------------------------
先转储undo segment header信息 （所谓的transaction table就包含在undo segment header中)， 首先从Transaction ID (XID) 或者从v$transaction中查询当前transaction位于哪个undo segment上。
SQL> select xid, xidusn, xidslot, xidsqn from v$transaction;
XID XIDUSN XIDSLOT XIDSQN
---------------- ---------- ---------- ----------
09000F0058B60000 9 15 46680
XID XIDUSN XIDSLOT XIDSQN
---------------- ---------- ---------- ----------
09000F0058B60000 9 15 46680
这跟刚才data block dump出来的itl中是XID是一致的 -- 0x0009.00f.0000b658 可以看出当前这个transaction位于第9个undo segment中的第15(0x00f)个slot。
通过如下SQL找出第九个undo segment是哪个...
SQL> select * from V$rollname;
USN NAME
---------- ------------------------------
0 SYSTEM
1 _SYSSMU1$
2 _SYSSMU2$
3 _SYSSMU3$
4 _SYSSMU4$
5 _SYSSMU5$
6 _SYSSMU6$
7 _SYSSMU7$
8 _SYSSMU8$
9 _SYSSMU9$
10 _SYSSMU10$
11 rows selected.
USN NAME
---------- ------------------------------
0 SYSTEM
1 _SYSSMU1$
2 _SYSSMU2$
3 _SYSSMU3$
4 _SYSSMU4$
5 _SYSSMU5$
6 _SYSSMU6$
7 _SYSSMU7$
8 _SYSSMU8$
9 _SYSSMU9$
10 _SYSSMU10$
11 rows selected.
现在转储第9个undo segment header...
SQL> alter system dump undo header '_SYSSMU9$';
System altered.
System altered.
查看下dump文件内容, 注意其中的transaction table (TRN TBL) 部分. (Retention Table应该跟undo retention相关， 在此不表）
********************************************************************************
Undo Segment: _SYSSMU9$ (9)
********************************************************************************
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 3 #blocks: 143
last map 0x00000000 #maps: 0 offset: 4080
Highwater:: 0x008007dd ext#: 2 blk#: 84 ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 2
Unlocked
Map Header:: next 0x00000000 #extents: 3 obj#: 0 flag: 0x40000000
Extent Map
-----------------------------------------------------------------
0x0080008a length: 7
0x00800081 length: 8
0x00800789 length: 128
Retention Table
-----------------------------------------------------------
Extent Number:0 Commit Time: 1299653569
Extent Number:1 Commit Time: 1299658037
Extent Number:2 Commit Time: 1299658037
TRN CTL:: seq: 0x2945 chd: 0x0014 ctl: 0x0010 inc: 0x00000000 nfb: 0x0000
mgc: 0x8201 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
uba: 0x008007dd.2945.05 scn: 0x0000.04025216
Version: 0x01
FREE BLOCK POOL::
uba: 0x00000000.2945.04 ext: 0x2 spc: 0x1dce
uba: 0x00000000.2945.02 ext: 0x2 spc: 0x1f0e
uba: 0x00000000.2945.19 ext: 0x2 spc: 0x9cc
uba: 0x00000000.27b0.01 ext: 0x2 spc: 0x1f88
uba: 0x00000000.0e8c.13 ext: 0x9 spc: 0x14e0
TRN TBL::
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
------------------------------------------------------------------------------------------------
0x00 9 0x00 0xb65b 0x0025 0x0000.040255ad 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672301
0x01 9 0x00 0xb648 0x0004 0x0000.040252b2 0x008007da 0x0000.000.00000000 0x00000001 0x00000000 1299671269
0x02 9 0x00 0xb656 0x0017 0x0000.0402522a 0x008007da 0x0000.000.00000000 0x00000001 0x00000000 1299671156
0x03 9 0x00 0xb652 0x002d 0x0000.0402551c 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672065
0x04 9 0x00 0xb668 0x000a 0x0000.040252f6 0x008007da 0x0000.000.00000000 0x00000001 0x00000000 1299671389
0x05 9 0x00 0xb659 0x0013 0x0000.04025686 0x008007dc 0x0000.000.00000000 0x00000001 0x00000000 1299672470
0x06 9 0x00 0xb667 0x0000 0x0000.040255a3 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672289
0x07 9 0x00 0xb668 0x001c 0x0000.040257d5 0x008007dc 0x0000.000.00000000 0x00000001 0x00000000 1299673070
0x08 9 0x00 0xb65e 0x001a 0x0000.04025606 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672357
0x09 9 0x00 0xb64b 0x0010 0x0000.0402582e 0x008007dd 0x0000.000.00000000 0x00000001 0x00000000 1299673192
0x0a 9 0x00 0xb658 0x0027 0x0000.04025313 0x008007da 0x0000.000.00000000 0x00000001 0x00000000 1299671449
0x0b 9 0x00 0xb656 0x0003 0x0000.040254a8 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672061
0x0c 9 0x00 0xb65d 0x000b 0x0000.04025488 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672050
0x0d 9 0x00 0xb655 0x002f 0x0000.0402525a 0x008007da 0x0000.000.00000000 0x00000001 0x00000000 1299671156
0x0e 9 0x00 0xb66c 0x0008 0x0000.040255fe 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672357
0x0f 10 0x80 0xb658 0x0002 0x0000.0402589a 0x008007dd 0x0000.000.00000000 0x00000001 0x00000000 0
0x10 9 0x00 0xb64d 0xffff 0x0000.04025888 0x008007dd 0x0000.000.00000000 0x00000001 0x00000000 1299673370
0x11 9 0x00 0xb659 0x001d 0x0000.040255d1 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672357
0x12 9 0x00 0xb664 0x0005 0x0000.04025676 0x008007dc 0x0000.000.00000000 0x00000001 0x00000000 1299672466
0x13 9 0x00 0xb64b 0x001e 0x0000.040256ca 0x008007dc 0x0000.000.00000000 0x00000001 0x00000000 1299672590
0x14 9 0x00 0xb661 0x0002 0x0000.0402521e 0x008007da 0x0000.000.00000000 0x00000001 0x00000000 1299671156
0x15 9 0x00 0xb65e 0x0020 0x0000.04025718 0x008007dc 0x0000.000.00000000 0x00000001 0x00000000 1299672754
0x16 9 0x00 0xb65c 0x0019 0x0000.04025449 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299671989
0x17 9 0x00 0xb664 0x000d 0x0000.0402523e 0x008007da 0x0000.000.00000000 0x00000001 0x00000000 1299671156
0x18 9 0x00 0xb65a 0x002a 0x0000.0402562f 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672357
0x19 9 0x00 0xb660 0x000c 0x0000.0402546c 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672049
0x1a 9 0x00 0xb668 0x0029 0x0000.04025616 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672357
0x1b 9 0x00 0xb65e 0x0016 0x0000.04025423 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299671929
0x1c 9 0x00 0xb659 0x0009 0x0000.040257fd 0x008007dc 0x0000.000.00000000 0x00000001 0x00000000 1299673130
0x1d 9 0x00 0xb643 0x0028 0x0000.040255f1 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672357
0x1e 9 0x00 0xb670 0x0015 0x0000.040256e5 0x008007dc 0x0000.000.00000000 0x00000001 0x00000000 1299672649
0x1f 9 0x00 0xb661 0x0006 0x0000.04025586 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672229
0x20 9 0x00 0xb63d 0x0026 0x0000.0402572b 0x008007dc 0x0000.000.00000000 0x00000001 0x00000000 1299672770
0x21 9 0x00 0xb666 0x0024 0x0000.04025362 0x008007da 0x0000.000.00000000 0x00000001 0x00000000 1299671569
0x22 9 0x00 0xb65e 0x0007 0x0000.040257cb 0x008007dc 0x0000.000.00000000 0x00000001 0x00000000 1299673069
0x23 9 0x00 0xb65a 0x001b 0x0000.040253e1 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299671810
0x24 9 0x00 0xb65e 0x0023 0x0000.040253c4 0x008007da 0x0000.000.00000000 0x00000001 0x00000000 1299671755
0x25 9 0x00 0xb663 0x0011 0x0000.040255ca 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672357
0x26 9 0x00 0x9b86 0x0022 0x0000.0402576b 0x008007dc 0x0000.000.00000000 0x00000001 0x00000000 1299672890
0x27 9 0x00 0xb614 0x0021 0x0000.0402534c 0x008007da 0x0000.000.00000000 0x00000001 0x00000000 1299671551
0x28 9 0x00 0xb64b 0x000e 0x0000.040255f5 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672357
0x29 9 0x00 0xb656 0x002b 0x0000.04025622 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672357
0x2a 9 0x00 0xb655 0x002c 0x0000.04025656 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672410
0x2b 9 0x00 0xb61f 0x0018 0x0000.04025626 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672357
0x2c 9 0x00 0xb65b 0x0012 0x0000.0402565c 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672410
0x2d 9 0x00 0xb64e 0x002e 0x0000.04025548 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672115
0x2e 9 0x00 0xb65c 0x001f 0x0000.04025561 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672169
0x2f 9 0x00 0xb662 0x0001 0x0000.0402526f 0x008007da 0x0000.000.00000000 0x00000001 0x00000000 1299671156
Undo Segment: _SYSSMU9$ (9)
********************************************************************************
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 3 #blocks: 143
last map 0x00000000 #maps: 0 offset: 4080
Highwater:: 0x008007dd ext#: 2 blk#: 84 ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 2
Unlocked
Map Header:: next 0x00000000 #extents: 3 obj#: 0 flag: 0x40000000
Extent Map
-----------------------------------------------------------------
0x0080008a length: 7
0x00800081 length: 8
0x00800789 length: 128
Retention Table
-----------------------------------------------------------
Extent Number:0 Commit Time: 1299653569
Extent Number:1 Commit Time: 1299658037
Extent Number:2 Commit Time: 1299658037
TRN CTL:: seq: 0x2945 chd: 0x0014 ctl: 0x0010 inc: 0x00000000 nfb: 0x0000
mgc: 0x8201 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
uba: 0x008007dd.2945.05 scn: 0x0000.04025216
Version: 0x01
FREE BLOCK POOL::
uba: 0x00000000.2945.04 ext: 0x2 spc: 0x1dce
uba: 0x00000000.2945.02 ext: 0x2 spc: 0x1f0e
uba: 0x00000000.2945.19 ext: 0x2 spc: 0x9cc
uba: 0x00000000.27b0.01 ext: 0x2 spc: 0x1f88
uba: 0x00000000.0e8c.13 ext: 0x9 spc: 0x14e0
TRN TBL::
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
------------------------------------------------------------------------------------------------
0x00 9 0x00 0xb65b 0x0025 0x0000.040255ad 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672301
0x01 9 0x00 0xb648 0x0004 0x0000.040252b2 0x008007da 0x0000.000.00000000 0x00000001 0x00000000 1299671269
0x02 9 0x00 0xb656 0x0017 0x0000.0402522a 0x008007da 0x0000.000.00000000 0x00000001 0x00000000 1299671156
0x03 9 0x00 0xb652 0x002d 0x0000.0402551c 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672065
0x04 9 0x00 0xb668 0x000a 0x0000.040252f6 0x008007da 0x0000.000.00000000 0x00000001 0x00000000 1299671389
0x05 9 0x00 0xb659 0x0013 0x0000.04025686 0x008007dc 0x0000.000.00000000 0x00000001 0x00000000 1299672470
0x06 9 0x00 0xb667 0x0000 0x0000.040255a3 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672289
0x07 9 0x00 0xb668 0x001c 0x0000.040257d5 0x008007dc 0x0000.000.00000000 0x00000001 0x00000000 1299673070
0x08 9 0x00 0xb65e 0x001a 0x0000.04025606 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672357
0x09 9 0x00 0xb64b 0x0010 0x0000.0402582e 0x008007dd 0x0000.000.00000000 0x00000001 0x00000000 1299673192
0x0a 9 0x00 0xb658 0x0027 0x0000.04025313 0x008007da 0x0000.000.00000000 0x00000001 0x00000000 1299671449
0x0b 9 0x00 0xb656 0x0003 0x0000.040254a8 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672061
0x0c 9 0x00 0xb65d 0x000b 0x0000.04025488 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672050
0x0d 9 0x00 0xb655 0x002f 0x0000.0402525a 0x008007da 0x0000.000.00000000 0x00000001 0x00000000 1299671156
0x0e 9 0x00 0xb66c 0x0008 0x0000.040255fe 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672357
0x0f 10 0x80 0xb658 0x0002 0x0000.0402589a 0x008007dd 0x0000.000.00000000 0x00000001 0x00000000 0
0x10 9 0x00 0xb64d 0xffff 0x0000.04025888 0x008007dd 0x0000.000.00000000 0x00000001 0x00000000 1299673370
0x11 9 0x00 0xb659 0x001d 0x0000.040255d1 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672357
0x12 9 0x00 0xb664 0x0005 0x0000.04025676 0x008007dc 0x0000.000.00000000 0x00000001 0x00000000 1299672466
0x13 9 0x00 0xb64b 0x001e 0x0000.040256ca 0x008007dc 0x0000.000.00000000 0x00000001 0x00000000 1299672590
0x14 9 0x00 0xb661 0x0002 0x0000.0402521e 0x008007da 0x0000.000.00000000 0x00000001 0x00000000 1299671156
0x15 9 0x00 0xb65e 0x0020 0x0000.04025718 0x008007dc 0x0000.000.00000000 0x00000001 0x00000000 1299672754
0x16 9 0x00 0xb65c 0x0019 0x0000.04025449 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299671989
0x17 9 0x00 0xb664 0x000d 0x0000.0402523e 0x008007da 0x0000.000.00000000 0x00000001 0x00000000 1299671156
0x18 9 0x00 0xb65a 0x002a 0x0000.0402562f 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672357
0x19 9 0x00 0xb660 0x000c 0x0000.0402546c 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672049
0x1a 9 0x00 0xb668 0x0029 0x0000.04025616 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672357
0x1b 9 0x00 0xb65e 0x0016 0x0000.04025423 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299671929
0x1c 9 0x00 0xb659 0x0009 0x0000.040257fd 0x008007dc 0x0000.000.00000000 0x00000001 0x00000000 1299673130
0x1d 9 0x00 0xb643 0x0028 0x0000.040255f1 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672357
0x1e 9 0x00 0xb670 0x0015 0x0000.040256e5 0x008007dc 0x0000.000.00000000 0x00000001 0x00000000 1299672649
0x1f 9 0x00 0xb661 0x0006 0x0000.04025586 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672229
0x20 9 0x00 0xb63d 0x0026 0x0000.0402572b 0x008007dc 0x0000.000.00000000 0x00000001 0x00000000 1299672770
0x21 9 0x00 0xb666 0x0024 0x0000.04025362 0x008007da 0x0000.000.00000000 0x00000001 0x00000000 1299671569
0x22 9 0x00 0xb65e 0x0007 0x0000.040257cb 0x008007dc 0x0000.000.00000000 0x00000001 0x00000000 1299673069
0x23 9 0x00 0xb65a 0x001b 0x0000.040253e1 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299671810
0x24 9 0x00 0xb65e 0x0023 0x0000.040253c4 0x008007da 0x0000.000.00000000 0x00000001 0x00000000 1299671755
0x25 9 0x00 0xb663 0x0011 0x0000.040255ca 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672357
0x26 9 0x00 0x9b86 0x0022 0x0000.0402576b 0x008007dc 0x0000.000.00000000 0x00000001 0x00000000 1299672890
0x27 9 0x00 0xb614 0x0021 0x0000.0402534c 0x008007da 0x0000.000.00000000 0x00000001 0x00000000 1299671551
0x28 9 0x00 0xb64b 0x000e 0x0000.040255f5 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672357
0x29 9 0x00 0xb656 0x002b 0x0000.04025622 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672357
0x2a 9 0x00 0xb655 0x002c 0x0000.04025656 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672410
0x2b 9 0x00 0xb61f 0x0018 0x0000.04025626 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672357
0x2c 9 0x00 0xb65b 0x0012 0x0000.0402565c 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672410
0x2d 9 0x00 0xb64e 0x002e 0x0000.04025548 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672115
0x2e 9 0x00 0xb65c 0x001f 0x0000.04025561 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672169
0x2f 9 0x00 0xb662 0x0001 0x0000.0402526f 0x008007da 0x0000.000.00000000 0x00000001 0x00000000 1299671156
在transaction table中找到第15（0x0f)个slot, 同时从state(10)也可以看出这个transaction是active状态的。同时注意到dba为0x008007dd， 这个刚好是undo block的rdba， wrap#为0xb658 （46680）， 刚好跟v$transaction中得到的XIDSQN是一样的。
查看undo block的dump info
---------------------------------------------
明白了undo segment header， 下面来看看undo block内容。
现在转储undo block （2， 2013）的内容看看...
SQL> alter system dump datafile 2 block 2013;
System altered.
System altered.
看看undo block的内容。 （注意 scn: 0x0000.04025991 和上面的data block的scn是一致的）
Start dump data blocks tsn: 1 file#: 2 minblk 2013 maxblk 2013
buffer tsn: 1 rdba: 0x008007dd (2/2013)
scn: 0x0000.04025991 seq: 0x01 flg: 0x04 tail: 0x59910201
frmt: 0x02 chkval: 0x0863 type: 0x02=KTU UNDO BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x08582200 to 0x08584200
.....
<omit something>
.....
********************************************************************************
UNDO BLK:
xid: 0x0009.00f.0000b658 seq: 0x2945 cnt: 0x7 irb: 0x7 icl: 0x0 flg: 0x0000
Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset
---------------------------------------------------------------------------
0x01 0x1f68 0x02 0x1ee0 0x03 0x1e60 0x04 0x1de8 0x05 0x1d4c
0x06 0x1cf0 0x07 0x1c98
*-----------------------------
* Rec #0x1 slt: 0x09 objn: 50291(0x0000c473) objd: 50291 tblspc: 2(0x00000002)
* Layer: 11 (Row) opc: 1 rci 0x00
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
uba: 0x008007dc.2945.39 ctl max scn: 0x0000.040251f9 prv tx scn: 0x0000.04025203
txn start scn: scn: 0x0000.0402582d logon user: 51
prev brb: 8390618 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
op: L itl: xid: 0x0005.015.00009c00 uba: 0x0080038e.273f.04
flg: C--- lkc: 0 scn: 0x0000.04025829
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x00c066d0 hdba: 0x00c066cb
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 71(0x47)
*-----------------------------
* Rec #0x2 slt: 0x09 objn: 50493(0x0000c53d) objd: 50493 tblspc: 2(0x00000002)
* Layer: 10 (Index) opc: 22 rci 0x01
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
index undo for leaf key operations
KTB Redo
op: 0x04 ver: 0x01
op: L itl: xid: 0x0009.01c.0000b659 uba: 0x008007dc.2945.3a
flg: C--- lkc: 0 scn: 0x0000.040257fd
Dump kdilk : itl=2, kdxlkflg=0x41 sdc=1 indexid=0xc06cdb block=0x00c09835
(kdxlpu): purge leaf row
key :(49):
13 41 47 45 4e 54 5f 53 54 41 54 55 53 5f 4d 41 52 4b 45 52 07 78 6f 03 09
15 14 36 0d 41 47 45 4e 54 53 5f 4d 41 52 4b 45 44 06 00 c0 66 d0 00 47
*-----------------------------
* Rec #0x3 slt: 0x10 objn: 50291(0x0000c473) objd: 50291 tblspc: 2(0x00000002)
* Layer: 11 (Row) opc: 1 rci 0x00
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
uba: 0x008007dd.2945.01 ctl max scn: 0x0000.04025203 prv tx scn: 0x0000.0402520d
txn start scn: scn: 0x0000.04025886 logon user: 51
prev brb: 8390618 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
op: L itl: xid: 0x0005.026.00009c0d uba: 0x0080038e.273f.06
flg: C--- lkc: 0 scn: 0x0000.0402584c
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x00c066d0 hdba: 0x00c066cb
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 76(0x4c)
*-----------------------------
* Rec #0x4 slt: 0x10 objn: 50493(0x0000c53d) objd: 50493 tblspc: 2(0x00000002)
* Layer: 10 (Index) opc: 22 rci 0x03
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
index undo for leaf key operations
KTB Redo
op: 0x04 ver: 0x01
op: L itl: xid: 0x0002.019.00009875 uba: 0x00800be9.2563.18
flg: C--- lkc: 0 scn: 0x0000.04025846
Dump kdilk : itl=2, kdxlkflg=0x41 sdc=0 indexid=0xc06cdb block=0x00c09832
(kdxlpu): purge leaf row
key :(36):
0c 4d 47 4d 54 5f 53 59 53 5f 4a 4f 42 07 78 6f 03 09 15 17 33 07 6d 65 74
72 69 63 73 06 00 c0 66 d0 00 4c
*-----------------------------
* Rec #0x5 slt: 0x0f objn: 354525(0x000568dd) objd: 354525 tblspc: 18(0x00000012)
* Layer: 11 (Row) opc: 1 rci 0x00
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
uba: 0x008007dd.2945.03 ctl max scn: 0x0000.0402520d prv tx scn: 0x0000.04025216
txn start scn: scn: 0x0000.04025792 logon user: 358
prev brb: 8390618 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
op: Z
Array Update of 1 rows:
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0
ncol: 2 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x03c00010 hdba: 0x03c0000b
itli: 2 ispac: 0 maxfr: 4858
vect = 3
col 1: [ 5] 46 72 61 6e 6b
*-----------------------------
* Rec #0x6 slt: 0x0f objn: 354525(0x000568dd) objd: 354525 tblspc: 18(0x00000012)
* Layer: 11 (Row) opc: 1 rci 0x05
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x008007dd.2945.05
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x03c00010 hdba: 0x03c0000b
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 2 ckix: 8
ncol: 2 nnew: 1 size: 1
col 1: [ 5] 46 72 61 75 64
*-----------------------------
* Rec #0x7 slt: 0x0f objn: 354525(0x000568dd) objd: 354525 tblspc: 18(0x00000012)
* Layer: 11 (Row) opc: 1 rci 0x06
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x008007dd.2945.06
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x03c00010 hdba: 0x03c0000b
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 2 ckix: 8
ncol: 2 nnew: 1 size: -1
col 1: [ 4] 54 65 73 74
End dump data blocks tsn: 1 file#: 2 minblk 2013 maxblk 2013
buffer tsn: 1 rdba: 0x008007dd (2/2013)
scn: 0x0000.04025991 seq: 0x01 flg: 0x04 tail: 0x59910201
frmt: 0x02 chkval: 0x0863 type: 0x02=KTU UNDO BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x08582200 to 0x08584200
.....
<omit something>
.....
********************************************************************************
UNDO BLK:
xid: 0x0009.00f.0000b658 seq: 0x2945 cnt: 0x7 irb: 0x7 icl: 0x0 flg: 0x0000
Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset
---------------------------------------------------------------------------
0x01 0x1f68 0x02 0x1ee0 0x03 0x1e60 0x04 0x1de8 0x05 0x1d4c
0x06 0x1cf0 0x07 0x1c98
*-----------------------------
* Rec #0x1 slt: 0x09 objn: 50291(0x0000c473) objd: 50291 tblspc: 2(0x00000002)
* Layer: 11 (Row) opc: 1 rci 0x00
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
uba: 0x008007dc.2945.39 ctl max scn: 0x0000.040251f9 prv tx scn: 0x0000.04025203
txn start scn: scn: 0x0000.0402582d logon user: 51
prev brb: 8390618 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
op: L itl: xid: 0x0005.015.00009c00 uba: 0x0080038e.273f.04
flg: C--- lkc: 0 scn: 0x0000.04025829
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x00c066d0 hdba: 0x00c066cb
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 71(0x47)
*-----------------------------
* Rec #0x2 slt: 0x09 objn: 50493(0x0000c53d) objd: 50493 tblspc: 2(0x00000002)
* Layer: 10 (Index) opc: 22 rci 0x01
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
index undo for leaf key operations
KTB Redo
op: 0x04 ver: 0x01
op: L itl: xid: 0x0009.01c.0000b659 uba: 0x008007dc.2945.3a
flg: C--- lkc: 0 scn: 0x0000.040257fd
Dump kdilk : itl=2, kdxlkflg=0x41 sdc=1 indexid=0xc06cdb block=0x00c09835
(kdxlpu): purge leaf row
key :(49):
13 41 47 45 4e 54 5f 53 54 41 54 55 53 5f 4d 41 52 4b 45 52 07 78 6f 03 09
15 14 36 0d 41 47 45 4e 54 53 5f 4d 41 52 4b 45 44 06 00 c0 66 d0 00 47
*-----------------------------
* Rec #0x3 slt: 0x10 objn: 50291(0x0000c473) objd: 50291 tblspc: 2(0x00000002)
* Layer: 11 (Row) opc: 1 rci 0x00
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
uba: 0x008007dd.2945.01 ctl max scn: 0x0000.04025203 prv tx scn: 0x0000.0402520d
txn start scn: scn: 0x0000.04025886 logon user: 51
prev brb: 8390618 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
op: L itl: xid: 0x0005.026.00009c0d uba: 0x0080038e.273f.06
flg: C--- lkc: 0 scn: 0x0000.0402584c
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x00c066d0 hdba: 0x00c066cb
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 76(0x4c)
*-----------------------------
* Rec #0x4 slt: 0x10 objn: 50493(0x0000c53d) objd: 50493 tblspc: 2(0x00000002)
* Layer: 10 (Index) opc: 22 rci 0x03
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
index undo for leaf key operations
KTB Redo
op: 0x04 ver: 0x01
op: L itl: xid: 0x0002.019.00009875 uba: 0x00800be9.2563.18
flg: C--- lkc: 0 scn: 0x0000.04025846
Dump kdilk : itl=2, kdxlkflg=0x41 sdc=0 indexid=0xc06cdb block=0x00c09832
(kdxlpu): purge leaf row
key :(36):
0c 4d 47 4d 54 5f 53 59 53 5f 4a 4f 42 07 78 6f 03 09 15 17 33 07 6d 65 74
72 69 63 73 06 00 c0 66 d0 00 4c
*-----------------------------
* Rec #0x5 slt: 0x0f objn: 354525(0x000568dd) objd: 354525 tblspc: 18(0x00000012)
* Layer: 11 (Row) opc: 1 rci 0x00
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
uba: 0x008007dd.2945.03 ctl max scn: 0x0000.0402520d prv tx scn: 0x0000.04025216
txn start scn: scn: 0x0000.04025792 logon user: 358
prev brb: 8390618 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
op: Z
Array Update of 1 rows:
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0
ncol: 2 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x03c00010 hdba: 0x03c0000b
itli: 2 ispac: 0 maxfr: 4858
vect = 3
col 1: [ 5] 46 72 61 6e 6b
*-----------------------------
* Rec #0x6 slt: 0x0f objn: 354525(0x000568dd) objd: 354525 tblspc: 18(0x00000012)
* Layer: 11 (Row) opc: 1 rci 0x05
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x008007dd.2945.05
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x03c00010 hdba: 0x03c0000b
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 2 ckix: 8
ncol: 2 nnew: 1 size: 1
col 1: [ 5] 46 72 61 75 64
*-----------------------------
* Rec #0x7 slt: 0x0f objn: 354525(0x000568dd) objd: 354525 tblspc: 18(0x00000012)
* Layer: 11 (Row) opc: 1 rci 0x06
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x008007dd.2945.06
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x03c00010 hdba: 0x03c0000b
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 2 ckix: 8
ncol: 2 nnew: 1 size: -1
col 1: [ 4] 54 65 73 74
End dump data blocks tsn: 1 file#: 2 minblk 2013 maxblk 2013
undo block的内容比较多，也很重要。要一段段来看， 首先是起始部分的UNDO BLK 部分...
********************************************************************************
UNDO BLK:
xid: 0x0009.00f.0000b658 seq: 0x2945 cnt: 0x7 irb: 0x7 icl: 0x0 flg: 0x0000
Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset
---------------------------------------------------------------------------
0x01 0x1f68 0x02 0x1ee0 0x03 0x1e60 0x04 0x1de8 0x05 0x1d4c
0x06 0x1cf0 0x07 0x1c98
UNDO BLK:
xid: 0x0009.00f.0000b658 seq: 0x2945 cnt: 0x7 irb: 0x7 icl: 0x0 flg: 0x0000
Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset
---------------------------------------------------------------------------
0x01 0x1f68 0x02 0x1ee0 0x03 0x1e60 0x04 0x1de8 0x05 0x1d4c
0x06 0x1cf0 0x07 0x1c98
********************************************************************************
注意irb:0x7表示undo block中最近为提交事务变更的开始之处(表示undo record 7, 和v$transaction的UBA中的信息一致）， 如果rollback这个事务，就从这个record开始查找。
从undo record的偏移量(offset)地址信息可以看出， 最后一个刚好是0x07的。
下面看看一下undo record 0x07的信息 (在dump的最底面, slt:0x0f 刚好表示是transaction table的第15个slot.
================================================================
*-----------------------------
* Rec #0x7 slt: 0x0f objn: 354525(0x000568dd) objd: 354525 tblspc: 18(0x00000012)
* Layer: 11 (Row) opc: 1 rci 0x06
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x008007dd.2945.06
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x03c00010 hdba: 0x03c0000b
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 2 ckix: 8
ncol: 2 nnew: 1 size: -1
col 1: [ 4] 54 65 73 74
* Rec #0x7 slt: 0x0f objn: 354525(0x000568dd) objd: 354525 tblspc: 18(0x00000012)
* Layer: 11 (Row) opc: 1 rci 0x06
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x008007dd.2945.06
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x03c00010 hdba: 0x03c0000b
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 2 ckix: 8
ncol: 2 nnew: 1 size: -1
col 1: [ 4] 54 65 73 74
===================================================================
这一行的信息刚好是最后一次更改(Test2) 的前镜像(before image) -- Test.
SQL> select dump('Test', 16) from dual;
DUMP('TEST',16)
-------------------------
Typ=96 Len=4: 54,65,73,74
DUMP('TEST',16)
-------------------------
Typ=96 Len=4: 54,65,73,74
同时注意uba: 0x008007dd.2945.06的06表示的是上一次更改的undo record的序号，也就是rci 0x06. Undo 就是通过这种关系(chain)来实现改动的回滚操作的。
那么接下来就看看 undo record 0x06的内容
=====================================================================
*-----------------------------
* Rec #0x6 slt: 0x0f objn: 354525(0x000568dd) objd: 354525 tblspc: 18(0x00000012)
* Layer: 11 (Row) opc: 1 rci 0x05
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x008007dd.2945.05
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x03c00010 hdba: 0x03c0000b
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 2 ckix: 8
ncol: 2 nnew: 1 size: 1
col 1: [ 5] 46 72 61 75 64
* Rec #0x6 slt: 0x0f objn: 354525(0x000568dd) objd: 354525 tblspc: 18(0x00000012)
* Layer: 11 (Row) opc: 1 rci 0x05
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x008007dd.2945.05
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x03c00010 hdba: 0x03c0000b
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 2 ckix: 8
ncol: 2 nnew: 1 size: 1
col 1: [ 5] 46 72 61 75 64
======================================================================
这一行的数据是'Test' 前一次更改('Fraud')
SQL> select dump('Fraud', 16) from dual;
DUMP('FRAUD',16)
----------------------------
Typ=96 Len=5: 46,72,61,75,64
DUMP('FRAUD',16)
----------------------------
Typ=96 Len=5: 46,72,61,75,64
根据undo chain, 找到下一个undo record 0x05
=======================================================================
*-----------------------------
* Rec #0x5 slt: 0x0f objn: 354525(0x000568dd) objd: 354525 tblspc: 18(0x00000012)
* Layer: 11 (Row) opc: 1 rci 0x00
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
uba: 0x008007dd.2945.03 ctl max scn: 0x0000.0402520d prv tx scn: 0x0000.04025216
txn start scn: scn: 0x0000.04025792 logon user: 358
prev brb: 8390618 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
op: Z
Array Update of 1 rows:
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0
ncol: 2 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x03c00010 hdba: 0x03c0000b
itli: 2 ispac: 0 maxfr: 4858
vect = 3
col 1: [ 5] 46 72 61 6e 6b
* Rec #0x5 slt: 0x0f objn: 354525(0x000568dd) objd: 354525 tblspc: 18(0x00000012)
* Layer: 11 (Row) opc: 1 rci 0x00
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
uba: 0x008007dd.2945.03 ctl max scn: 0x0000.0402520d prv tx scn: 0x0000.04025216
txn start scn: scn: 0x0000.04025792 logon user: 358
prev brb: 8390618 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
op: Z
Array Update of 1 rows:
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0
ncol: 2 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x03c00010 hdba: 0x03c0000b
itli: 2 ispac: 0 maxfr: 4858
vect = 3
col 1: [ 5] 46 72 61 6e 6b
========================================================================
这一行的数据是'Fraud'之前的镜像，也就是源数据'Frank', 这个同时也说明了回退到了这个transaction的起点。
SQL> select dump('Frank', 16) from dual;
DUMP('FRANK',16)
----------------------------
Typ=96 Len=5: 46,72,61,6e,6b
DUMP('FRANK',16)
----------------------------
Typ=96 Len=5: 46,72,61,6e,6b
从rci 0x00 也可以得到这个是undo chain的最后一条记录了。而且注意到recod 05中没有uba的信息。
下面看看延时块清除的情况(delayed block cleanout)
---------------------------------------------
所谓delayed block cleanout指的是在transaction commit的时候，因为block已经写入datafile不存在buffer cache中(或者buffer cache中的修改的block太多，超过10%的buffer cache)，那么就不会修改data block中的ITL标志位，而是等到下次再访问到该block的时候再进行data block的cleanout操作。这样显然效率会高很多。
现在尝试刷新buffer cache, 然后再commit事务，这样在下次访问该block的时候会发生delayed block cleanout操作了。
SQL> alter system flush buffer_cache;
System altered.
SQL> commit;
Commit complete.
SQL> alter system dump datafile 2 block 2013; --- undo block
System altered.
SQL> alter system dump datafile 15 block 16; --- data block
System altered.
SQL> alter system dump undo header '_SYSSMU9$'; --- undo segment header
System altered.
SQL>
System altered.
SQL> commit;
Commit complete.
SQL> alter system dump datafile 2 block 2013; --- undo block
System altered.
SQL> alter system dump datafile 15 block 16; --- data block
System altered.
SQL> alter system dump undo header '_SYSSMU9$'; --- undo segment header
System altered.
SQL>
首先看一下undo segment header内容....
-- undo segment header
********************************************************************************
Undo Segment: _SYSSMU9$ (9)
********************************************************************************
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 3 #blocks: 143
last map 0x00000000 #maps: 0 offset: 4080
Highwater:: 0x008007dd ext#: 2 blk#: 84 ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 2
Unlocked
Map Header:: next 0x00000000 #extents: 3 obj#: 0 flag: 0x40000000
Extent Map
-----------------------------------------------------------------
0x0080008a length: 7
0x00800081 length: 8
0x00800789 length: 128
Retention Table
-----------------------------------------------------------
Extent Number:0 Commit Time: 1299653569
Extent Number:1 Commit Time: 1299658037
Extent Number:2 Commit Time: 1299658037
TRN CTL:: seq: 0x2945 chd: 0x0014 ctl: 0x000f inc: 0x00000000 nfb: 0x0001
mgc: 0x8201 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
uba: 0x008007dd.2945.05 scn: 0x0000.04025216
Version: 0x01
FREE BLOCK POOL::
uba: 0x008007dd.2945.07 ext: 0x2 spc: 0x1c78
uba: 0x00000000.2945.02 ext: 0x2 spc: 0x1f0e
uba: 0x00000000.2945.19 ext: 0x2 spc: 0x9cc
uba: 0x00000000.27b0.01 ext: 0x2 spc: 0x1f88
uba: 0x00000000.0e8c.13 ext: 0x9 spc: 0x14e0
TRN TBL::
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
------------------------------------------------------------------------------------------------
0x00 9 0x00 0xb65b 0x0025 0x0000.040255ad 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672301
0x01 9 0x00 0xb648 0x0004 0x0000.040252b2 0x008007da 0x0000.000.00000000 0x00000001 0x00000000 1299671269
0x02 9 0x00 0xb656 0x0017 0x0000.0402522a 0x008007da 0x0000.000.00000000 0x00000001 0x00000000 1299671156
0x03 9 0x00 0xb652 0x002d 0x0000.0402551c 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672065
0x04 9 0x00 0xb668 0x000a 0x0000.040252f6 0x008007da 0x0000.000.00000000 0x00000001 0x00000000 1299671389
0x05 9 0x00 0xb659 0x0013 0x0000.04025686 0x008007dc 0x0000.000.00000000 0x00000001 0x00000000 1299672470
0x06 9 0x00 0xb667 0x0000 0x0000.040255a3 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672289
0x07 9 0x00 0xb668 0x001c 0x0000.040257d5 0x008007dc 0x0000.000.00000000 0x00000001 0x00000000 1299673070
0x08 9 0x00 0xb65e 0x001a 0x0000.04025606 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672357
0x09 9 0x00 0xb64b 0x0010 0x0000.0402582e 0x008007dd 0x0000.000.00000000 0x00000001 0x00000000 1299673192
0x0a 9 0x00 0xb658 0x0027 0x0000.04025313 0x008007da 0x0000.000.00000000 0x00000001 0x00000000 1299671449
0x0b 9 0x00 0xb656 0x0003 0x0000.040254a8 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672061
0x0c 9 0x00 0xb65d 0x000b 0x0000.04025488 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672050
0x0d 9 0x00 0xb655 0x002f 0x0000.0402525a 0x008007da 0x0000.000.00000000 0x00000001 0x00000000 1299671156
0x0e 9 0x00 0xb66c 0x0008 0x0000.040255fe 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672357
0x0f 9 0x00 0xb658 0xffff 0x0000.0403ac99 0x008007dd 0x0000.000.00000000 0x00000001 0x00000000 1299763055
0x10 9 0x00 0xb64d 0x000f 0x0000.04025888 0x008007dd 0x0000.000.00000000 0x00000001 0x00000000 1299673370
0x11 9 0x00 0xb659 0x001d 0x0000.040255d1 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672357
0x12 9 0x00 0xb664 0x0005 0x0000.04025676 0x008007dc 0x0000.000.00000000 0x00000001 0x00000000 1299672466
0x13 9 0x00 0xb64b 0x001e 0x0000.040256ca 0x008007dc 0x0000.000.00000000 0x00000001 0x00000000 1299672590
0x14 9 0x00 0xb661 0x0002 0x0000.0402521e 0x008007da 0x0000.000.00000000 0x00000001 0x00000000 1299671156
0x15 9 0x00 0xb65e 0x0020 0x0000.04025718 0x008007dc 0x0000.000.00000000 0x00000001 0x00000000 1299672754
0x16 9 0x00 0xb65c 0x0019 0x0000.04025449 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299671989
0x17 9 0x00 0xb664 0x000d 0x0000.0402523e 0x008007da 0x0000.000.00000000 0x00000001 0x00000000 1299671156
0x18 9 0x00 0xb65a 0x002a 0x0000.0402562f 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672357
0x19 9 0x00 0xb660 0x000c 0x0000.0402546c 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672049
0x1a 9 0x00 0xb668 0x0029 0x0000.04025616 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672357
0x1b 9 0x00 0xb65e 0x0016 0x0000.04025423 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299671929
0x1c 9 0x00 0xb659 0x0009 0x0000.040257fd 0x008007dc 0x0000.000.00000000 0x00000001 0x00000000 1299673130
0x1d 9 0x00 0xb643 0x0028 0x0000.040255f1 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672357
0x1e 9 0x00 0xb670 0x0015 0x0000.040256e5 0x008007dc 0x0000.000.00000000 0x00000001 0x00000000 1299672649
0x1f 9 0x00 0xb661 0x0006 0x0000.04025586 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672229
0x20 9 0x00 0xb63d 0x0026 0x0000.0402572b 0x008007dc 0x0000.000.00000000 0x00000001 0x00000000 1299672770
0x21 9 0x00 0xb666 0x0024 0x0000.04025362 0x008007da 0x0000.000.00000000 0x00000001 0x00000000 1299671569
0x22 9 0x00 0xb65e 0x0007 0x0000.040257cb 0x008007dc 0x0000.000.00000000 0x00000001 0x00000000 1299673069
0x23 9 0x00 0xb65a 0x001b 0x0000.040253e1 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299671810
0x24 9 0x00 0xb65e 0x0023 0x0000.040253c4 0x008007da 0x0000.000.00000000 0x00000001 0x00000000 1299671755
0x25 9 0x00 0xb663 0x0011 0x0000.040255ca 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672357
0x26 9 0x00 0x9b86 0x0022 0x0000.0402576b 0x008007dc 0x0000.000.00000000 0x00000001 0x00000000 1299672890
0x27 9 0x00 0xb614 0x0021 0x0000.0402534c 0x008007da 0x0000.000.00000000 0x00000001 0x00000000 1299671551
0x28 9 0x00 0xb64b 0x000e 0x0000.040255f5 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672357
0x29 9 0x00 0xb656 0x002b 0x0000.04025622 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672357
0x2a 9 0x00 0xb655 0x002c 0x0000.04025656 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672410
0x2b 9 0x00 0xb61f 0x0018 0x0000.04025626 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672357
0x2c 9 0x00 0xb65b 0x0012 0x0000.0402565c 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672410
0x2d 9 0x00 0xb64e 0x002e 0x0000.04025548 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672115
0x2e 9 0x00 0xb65c 0x001f 0x0000.04025561 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672169
0x2f 9 0x00 0xb662 0x0001 0x0000.0402526f 0x008007da 0x0000.000.00000000 0x00000001 0x00000000 1299671156
Undo Segment: _SYSSMU9$ (9)
********************************************************************************
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 3 #blocks: 143
last map 0x00000000 #maps: 0 offset: 4080
Highwater:: 0x008007dd ext#: 2 blk#: 84 ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 2
Unlocked
Map Header:: next 0x00000000 #extents: 3 obj#: 0 flag: 0x40000000
Extent Map
-----------------------------------------------------------------
0x0080008a length: 7
0x00800081 length: 8
0x00800789 length: 128
Retention Table
-----------------------------------------------------------
Extent Number:0 Commit Time: 1299653569
Extent Number:1 Commit Time: 1299658037
Extent Number:2 Commit Time: 1299658037
TRN CTL:: seq: 0x2945 chd: 0x0014 ctl: 0x000f inc: 0x00000000 nfb: 0x0001
mgc: 0x8201 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
uba: 0x008007dd.2945.05 scn: 0x0000.04025216
Version: 0x01
FREE BLOCK POOL::
uba: 0x008007dd.2945.07 ext: 0x2 spc: 0x1c78
uba: 0x00000000.2945.02 ext: 0x2 spc: 0x1f0e
uba: 0x00000000.2945.19 ext: 0x2 spc: 0x9cc
uba: 0x00000000.27b0.01 ext: 0x2 spc: 0x1f88
uba: 0x00000000.0e8c.13 ext: 0x9 spc: 0x14e0
TRN TBL::
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
------------------------------------------------------------------------------------------------
0x00 9 0x00 0xb65b 0x0025 0x0000.040255ad 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672301
0x01 9 0x00 0xb648 0x0004 0x0000.040252b2 0x008007da 0x0000.000.00000000 0x00000001 0x00000000 1299671269
0x02 9 0x00 0xb656 0x0017 0x0000.0402522a 0x008007da 0x0000.000.00000000 0x00000001 0x00000000 1299671156
0x03 9 0x00 0xb652 0x002d 0x0000.0402551c 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672065
0x04 9 0x00 0xb668 0x000a 0x0000.040252f6 0x008007da 0x0000.000.00000000 0x00000001 0x00000000 1299671389
0x05 9 0x00 0xb659 0x0013 0x0000.04025686 0x008007dc 0x0000.000.00000000 0x00000001 0x00000000 1299672470
0x06 9 0x00 0xb667 0x0000 0x0000.040255a3 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672289
0x07 9 0x00 0xb668 0x001c 0x0000.040257d5 0x008007dc 0x0000.000.00000000 0x00000001 0x00000000 1299673070
0x08 9 0x00 0xb65e 0x001a 0x0000.04025606 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672357
0x09 9 0x00 0xb64b 0x0010 0x0000.0402582e 0x008007dd 0x0000.000.00000000 0x00000001 0x00000000 1299673192
0x0a 9 0x00 0xb658 0x0027 0x0000.04025313 0x008007da 0x0000.000.00000000 0x00000001 0x00000000 1299671449
0x0b 9 0x00 0xb656 0x0003 0x0000.040254a8 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672061
0x0c 9 0x00 0xb65d 0x000b 0x0000.04025488 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672050
0x0d 9 0x00 0xb655 0x002f 0x0000.0402525a 0x008007da 0x0000.000.00000000 0x00000001 0x00000000 1299671156
0x0e 9 0x00 0xb66c 0x0008 0x0000.040255fe 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672357
0x0f 9 0x00 0xb658 0xffff 0x0000.0403ac99 0x008007dd 0x0000.000.00000000 0x00000001 0x00000000 1299763055
0x10 9 0x00 0xb64d 0x000f 0x0000.04025888 0x008007dd 0x0000.000.00000000 0x00000001 0x00000000 1299673370
0x11 9 0x00 0xb659 0x001d 0x0000.040255d1 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672357
0x12 9 0x00 0xb664 0x0005 0x0000.04025676 0x008007dc 0x0000.000.00000000 0x00000001 0x00000000 1299672466
0x13 9 0x00 0xb64b 0x001e 0x0000.040256ca 0x008007dc 0x0000.000.00000000 0x00000001 0x00000000 1299672590
0x14 9 0x00 0xb661 0x0002 0x0000.0402521e 0x008007da 0x0000.000.00000000 0x00000001 0x00000000 1299671156
0x15 9 0x00 0xb65e 0x0020 0x0000.04025718 0x008007dc 0x0000.000.00000000 0x00000001 0x00000000 1299672754
0x16 9 0x00 0xb65c 0x0019 0x0000.04025449 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299671989
0x17 9 0x00 0xb664 0x000d 0x0000.0402523e 0x008007da 0x0000.000.00000000 0x00000001 0x00000000 1299671156
0x18 9 0x00 0xb65a 0x002a 0x0000.0402562f 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672357
0x19 9 0x00 0xb660 0x000c 0x0000.0402546c 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672049
0x1a 9 0x00 0xb668 0x0029 0x0000.04025616 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672357
0x1b 9 0x00 0xb65e 0x0016 0x0000.04025423 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299671929
0x1c 9 0x00 0xb659 0x0009 0x0000.040257fd 0x008007dc 0x0000.000.00000000 0x00000001 0x00000000 1299673130
0x1d 9 0x00 0xb643 0x0028 0x0000.040255f1 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672357
0x1e 9 0x00 0xb670 0x0015 0x0000.040256e5 0x008007dc 0x0000.000.00000000 0x00000001 0x00000000 1299672649
0x1f 9 0x00 0xb661 0x0006 0x0000.04025586 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672229
0x20 9 0x00 0xb63d 0x0026 0x0000.0402572b 0x008007dc 0x0000.000.00000000 0x00000001 0x00000000 1299672770
0x21 9 0x00 0xb666 0x0024 0x0000.04025362 0x008007da 0x0000.000.00000000 0x00000001 0x00000000 1299671569
0x22 9 0x00 0xb65e 0x0007 0x0000.040257cb 0x008007dc 0x0000.000.00000000 0x00000001 0x00000000 1299673069
0x23 9 0x00 0xb65a 0x001b 0x0000.040253e1 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299671810
0x24 9 0x00 0xb65e 0x0023 0x0000.040253c4 0x008007da 0x0000.000.00000000 0x00000001 0x00000000 1299671755
0x25 9 0x00 0xb663 0x0011 0x0000.040255ca 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672357
0x26 9 0x00 0x9b86 0x0022 0x0000.0402576b 0x008007dc 0x0000.000.00000000 0x00000001 0x00000000 1299672890
0x27 9 0x00 0xb614 0x0021 0x0000.0402534c 0x008007da 0x0000.000.00000000 0x00000001 0x00000000 1299671551
0x28 9 0x00 0xb64b 0x000e 0x0000.040255f5 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672357
0x29 9 0x00 0xb656 0x002b 0x0000.04025622 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672357
0x2a 9 0x00 0xb655 0x002c 0x0000.04025656 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672410
0x2b 9 0x00 0xb61f 0x0018 0x0000.04025626 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672357
0x2c 9 0x00 0xb65b 0x0012 0x0000.0402565c 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672410
0x2d 9 0x00 0xb64e 0x002e 0x0000.04025548 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672115
0x2e 9 0x00 0xb65c 0x001f 0x0000.04025561 0x008007db 0x0000.000.00000000 0x00000001 0x00000000 1299672169
0x2f 9 0x00 0xb662 0x0001 0x0000.0402526f 0x008007da 0x0000.000.00000000 0x00000001 0x00000000 1299671156
注意到slot 15的状态已经变成了 9，不再是active（10）状态了，说明该事务已经被提交了。
接下来看一下 data block(15, 16)的dump内容...
Start dump data blocks tsn: 18 file#: 15 minblk 16 maxblk 16
buffer tsn: 18 rdba: 0x03c00010 (15/16)
scn: 0x0000.0403aa4d seq: 0x01 flg: 0x04 tail: 0xaa4d0601
frmt: 0x02 chkval: 0x535b type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0B082200 to 0x0B084200
.......
Block header dump: 0x03c00010
Object id on Block? Y
seg/obj: 0x568dd csc: 0x00.403aa4d itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x3c00009 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0001.000.000077b3 0x00800195.230a.19 C--- 0 scn 0x0000.04023fde
0x02 0x0009.00f.0000b658 0x008007dd.2945.07 ---- 1 fsc 0x0000.00000000
data_block_dump,data header at 0xb082264
===============
tsiz: 0x1f98
hsiz: 0x14
pbl: 0x0b082264
bdba: 0x03c00010
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f75
avsp=0x1f78
tosp=0x1f78
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x1f75
block_row_dump:
tab 0, row 0, @0x1f75
tl: 12 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 02
col 1: [ 5] 54 65 73 74 32
end_of_block_dump
End dump data blocks tsn: 18 file#: 15 minblk 16 maxblk 16
buffer tsn: 18 rdba: 0x03c00010 (15/16)
scn: 0x0000.0403aa4d seq: 0x01 flg: 0x04 tail: 0xaa4d0601
frmt: 0x02 chkval: 0x535b type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0B082200 to 0x0B084200
.......
Block header dump: 0x03c00010
Object id on Block? Y
seg/obj: 0x568dd csc: 0x00.403aa4d itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x3c00009 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0001.000.000077b3 0x00800195.230a.19 C--- 0 scn 0x0000.04023fde
0x02 0x0009.00f.0000b658 0x008007dd.2945.07 ---- 1 fsc 0x0000.00000000
data_block_dump,data header at 0xb082264
===============
tsiz: 0x1f98
hsiz: 0x14
pbl: 0x0b082264
bdba: 0x03c00010
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f75
avsp=0x1f78
tosp=0x1f78
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x1f75
block_row_dump:
tab 0, row 0, @0x1f75
tl: 12 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 02
col 1: [ 5] 54 65 73 74 32
end_of_block_dump
End dump data blocks tsn: 18 file#: 15 minblk 16 maxblk 16
注意这个时候的data block除了scn发生了变化（从0x0000.04025991 变成了0x0000.0403aa4d ），ITL slot 2的内容和commit之前是一样的，标志位并没有改成"commit"状态。这就说明了在commit的时候，因为block的内容已经被写回了数据文件，出于性能考虑，没有把block再读会buffer cache进行itl标志位的更改。
那么如果这个时候尝试访问表TEST的话，会触发物理读操作，将数据块重新load进buffer cache, 在这个时候会发生delayed block cleanout操作(根据itl slot中的xid找到undo segment header发现事物已经被commit了)， 注意block cleanout操作是会产生redo的，可以从执行计划统计信息中看出来...
SQL> set autotrace on;
SQL> select * from test;
ID NAME
---------- ----------
1 Test2
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TEST | 1 | 9 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
249 recursive calls
0 db block gets
41 consistent gets
15 physical reads
116 redo size
465 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select * from test;
ID NAME
---------- ----------
1 Test2
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TEST | 1 | 9 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
249 recursive calls
0 db block gets
41 consistent gets
15 physical reads
116 redo size
465 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed
从statistics部分可以看出一个简单的select语句居然产生了116 bytes redo！这个就是由block cleanout（itl slot update）引起的。如果执行这条查询语句就会发现不会产生redo了，
SQL> select * from test;
ID NAME
---------- ----------
1 Test2
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TEST | 1 | 9 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
465 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
ID NAME
---------- ----------
1 Test2
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TEST | 1 | 9 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
465 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
那么这个时候再来查看下datafile 15 block 16的内容，就会发现ITL已经被更新了...
Start dump data blocks tsn: 18 file#: 15 minblk 16 maxblk 16
buffer tsn: 18 rdba: 0x03c00010 (15/16)
scn: 0x0000.0408e294 seq: 0x01 flg: 0x04 tail: 0xe2940601
frmt: 0x02 chkval: 0x64e0 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0B082200 to 0x0B084200
<omit something for easy reading>
...
Block header dump: 0x03c00010
Object id on Block? Y
seg/obj: 0x568dd csc: 0x00.408e294 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x3c00009 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0001.000.000077b3 0x00800195.230a.19 C--- 0 scn 0x0000.04023fde
0x02 0x0009.00f.0000b658 0x008007dd.2945.07 C-U- 0 scn 0x0000.0408db69
data_block_dump,data header at 0xb082264
===============
tsiz: 0x1f98
hsiz: 0x14
pbl: 0x0b082264
bdba: 0x03c00010
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f75
avsp=0x1f78
tosp=0x1f78
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x1f75
block_row_dump:
tab 0, row 0, @0x1f75
tl: 12 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 02
col 1: [ 5] 54 65 73 74 32
end_of_block_dump
End dump data blocks tsn: 18 file#: 15 minblk 16 maxblk 16
buffer tsn: 18 rdba: 0x03c00010 (15/16)
scn: 0x0000.0408e294 seq: 0x01 flg: 0x04 tail: 0xe2940601
frmt: 0x02 chkval: 0x64e0 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0B082200 to 0x0B084200
<omit something for easy reading>
...
Block header dump: 0x03c00010
Object id on Block? Y
seg/obj: 0x568dd csc: 0x00.408e294 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x3c00009 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0001.000.000077b3 0x00800195.230a.19 C--- 0 scn 0x0000.04023fde
0x02 0x0009.00f.0000b658 0x008007dd.2945.07 C-U- 0 scn 0x0000.0408db69
data_block_dump,data header at 0xb082264
===============
tsiz: 0x1f98
hsiz: 0x14
pbl: 0x0b082264
bdba: 0x03c00010
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f75
avsp=0x1f78
tosp=0x1f78
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x1f75
block_row_dump:
tab 0, row 0, @0x1f75
tl: 12 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 02
col 1: [ 5] 54 65 73 74 32
end_of_block_dump
End dump data blocks tsn: 18 file#: 15 minblk 16 maxblk 16
可以看到ITL slot2的flag发生了变化，变成了C-U- 表示发生了delayed block clean out操作。如果这个时候再dump下undo block（2， 2013）的话，会发现irb发生了变化，不再是0x7了，表示之前的transacation已经commit了，不可以rollback了。
--------------------------------------
Regards,
FangwenYu
