oracle undo系列(三)
发生事务时，会先在数据块上分配一个itl条目;于是产生了本文：
我想到几个问题：
1,更新同一个表的同一条记录多次，会占用一条还是多条itl条目
2,更新同一个表的不同记录，是否会对应多条itl条目
先看第1个问题：
---会话1
SQL> create table t_undo(a int);
Table created
SQL> insert into t_undo values(1);
1 row inserted
SQL> commit;
Commit complete
---会话2
SQL> update t_undo set a=2;
1 row updated.
SQL> update t_undo set a=3;
1 row updated.
SQL> update t_undo set a=4;
1 row updated.
---会话1(注：如看不到单块读：alter system flush buffer_cache)
WAIT #6: nam='db file sequential read' ela= 571 file#=10 block#=276618 blocks=1 obj#=69928 tim=26973873653
WAIT #6: nam='db file scattered read' ela= 468 file#=10 block#=276619 blocks=5 obj#=69928 tim=26973874509
*** 2013-03-21 16:47:25.979
WAIT #6: nam='db file sequential read' ela= 126477 file#=3 block#=2656 blocks=1 obj#=0 tim=26974001159
WAIT #6: nam='db file sequential read' ela= 450 file#=3 block#=113578 blocks=1 obj#=0 tim=26974001933
--会话1
SQL> select dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from t_undo;
DBMS_ROWID.ROWID_RELATIVE_FNO( DBMS_ROWID.ROWID_BLOCK_NUMBER(
------------------------------ ------------------------------
10 276619
--会话1,276618为段头块,由上trace可知先读段头，后读数据块；
SQL> select header_file,header_block from dba_segments where segment_name='T_UNDO';
HEADER_FILE HEADER_BLOCK
----------- ------------
10 276618
--会话1：依次读了段头块和数据块后，还分别单块读了3号文件的2个块,我们看看2两个块
---可知2656块是回滚段头块
scn: 0x0000.008a5e9b seq: 0x01 flg: 0x04 tail: 0x5e9b2601
frmt: 0x02 chkval: 0x5529 type: 0x26=KTU SMU HEADER BLOCK
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 13 #blocks: 1423
last map 0x00000000 #maps: 0 offset: 4080
Highwater:: 0x00c1bbaa ext#: 7 blk#: 42 ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 7
Unlocked
Map Header:: next 0x00000000 #extents: 13 obj#: 0 flag: 0x40000000
Extent Map
-----------------------------------------------------------------
0x00c00a61 length: 7
0x00c004d0 length: 8
--中间略
0x00c0ff00 length: 128
0x00c14500 length: 128
0x00c18700 length: 128
0x00c19480 length: 128
Retention Table
-----------------------------------------------------------
Extent Number:0 Commit Time: 1363853239
Extent Number:1 Commit Time: 1363853239
--中间略
Extent Number:11 Commit Time: 1363853239
Extent Number:12 Commit Time: 1363853239
TRN CTL:: seq: 0x0136 chd: 0x000b ctl: 0x0019 inc: 0x00000000 nfb: 0x0000
mgc: 0xb000 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
uba: 0x00c1bbaa.0136.4e scn: 0x0000.0088ddf2
Version: 0x01
FREE BLOCK POOL::
uba: 0x00000000.0136.4d ext: 0x7 spc: 0x98c
uba: 0x00000000.0120.02 ext: 0x4 spc: 0x1f0e
uba: 0x00000000.0120.01 ext: 0x4 spc: 0x1eb0
uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0
uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0
TRN TBL::
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
------------------------------------------------------------------------------------------------
0x00 9 0x00 0x007d 0x001d 0x0000.008a0bfc 0x00c09e87 0x0000.000.00000000 0x00000001 0x00000000 1363853920
0x01 9 0x00 0x007d 0x000d 0x0000.008937ab 0x00c1453a 0x0000.000.00000000 0x00000001 0x00000000 1363853099
0x02 9 0x00 0x007b 0x000a 0x0000.0089ba23 0x00c0d342 0x0000.000.00000000 0x00000052 0x00000000 1363853762
中间略
0x21 9 0x00 0x007e 0x0011 0x0000.008a5735 0x00c1bbaa 0x0000.000.00000000 0x00000001 0x00000000 1363854058
EXT TRN CTL::
usn: 23
sp1:0x00000000 sp2:0x00000000 sp3:0x00000000 sp4:0x00000000
sp5:0x00000000 sp6:0x00000000 sp7:0x00000000 sp8:0x00000000
EXT TRN TBL::
index extflag extHash extSpare1 extSpare2
---------------------------------------------------
0x00 0x00000000 0x00000000 0x00000000 0x00000000
0x01 0x00000000 0x00000000 0x00000000 0x00000000
中间略
0x21 0x00000000 0x00000000 0x00000000 0x00000000
End dump data blocks tsn: 2 file#: 3 minblk 2656 maxblk 2656
--dump另一个113578数据块,可知是具体的撤消数据块
scn: 0x0000.008a5e9e seq: 0x01 flg: 0x04 tail: 0x5e9e0201
frmt: 0x02 chkval: 0x1e54 type: 0x02=KTU UNDO BLOCK
--最新的撤消记录条目，转换为3,而不是4;4的撤消记录条目呢
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
compat bit: 4 (post-11) padding: 0
op: C uba: 0x00c1bbaa.0136.4f
Array Update of 1 rows:
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 2 ckix: 0
ncol: 1 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x0284388b hdba: 0x0284388a
itli: 2 ispac: 0 maxfr: 4858
vect = 0
col 0: [ 2] c1 04
--我们再dump下表的数据块
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000e.010.000000b0 0x00c1c212.017b.3f C--- 0 scn 0x0000.008a5e94
0x02 0x0017.014.0000007e 0x00c1bbaa.0136.50 ---- 1 fsc 0x0000.00000000
SQL> select to_number('00c1bbaa','XXXXXXXXXXXXXXX') from dual
2 ;
TO_NUMBER('00C04173','XXXXXXXX
------------------------------
12696490
--然后再把上述的值用dbms_utility转换为撤消表空间的文件号及数据块号,最后再dump
SQL> select dbms_utility.data_block_address_file(12696490), dbms_utility.data_block_address_block(12696490) from dual;
DBMS_UTILITY.DATA_BLOCK_ADDRES DBMS_UTILITY.DATA_BLOCK_ADDRES
------------------------------ ------------------------------
3 113578 --即本文开头10046 trace单块读的块，undo block
--再dump下上述的itl的uba ,根据itl直接定位到如下撤消记录条目
* Rec #0x50 slt: 0x14 objn: 69928(0x00011128) objd: 69929 tblspc: 8(0x00000008)
* Layer: 11 (Row) opc: 1 rci 0x4f
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
compat bit: 4 (post-11) padding: 0
op: C uba: 0x00c1bbaa.0136.4f
Array Update of 1 rows:
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 2 ckix: 0
ncol: 1 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x0284388b hdba: 0x0284388a
itli: 2 ispac: 0 maxfr: 4858
vect = 0
col 0: [ 2] c1 04 即找到前镜像
小结；多个事务对一个表同条记录dml，仅占用一个itl条目
--再看第2个问题：更新同一个表的不同记录，是否会对应多条itl条目
--准备测试环境略：基于上述增加一条记录
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0024.017.00000083 0x00c0f485.013c.0a --U- 1 fsc 0x0000.008a69bf
0x02 0x0011.018.000000f6 0x00c1b70b.0199.08 --U- 1 fsc 0x0000.008a6802
小结：更新同一个表不同记录会占用多条itl条目
78
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
