1.问题描述
当A会话修改一个块第一行数据，该块有10行，提交后B会话修改第二行，
那么按块结构undo中应存在两个undo块，每个块包含10行数据，而不是一个undo块，保存A和B会话修改两行数据的旧值?
2.实验结论
1、当Oracle数据库undo剩余空间小于100%时，Oracle为每个事务分配一个新的回滚段，初始为2个extent各8 blocks;
(undo段分配规则参见以下实验文档：http://blog.itpub.net/31442014/viewspace-2680390/)
2、undo中只存储修改前的before image,不记录未修改的其它行和列。
3.实验过程
create table orastar.undo_test(c1 char(10)) tablespace users;
insert into orastar.undo_test values ('a');
insert into orastar.undo_test values ('aa');
insert into orastar.undo_test values ('aaa');
insert into orastar.undo_test values ('aaaa');
insert into orastar.undo_test values ('aaaaa');
insert into orastar.undo_test values ('aaaaaa');
insert into orastar.undo_test values ('aaaaaaa');
insert into orastar.undo_test values ('aaaaaaaa');
insert into orastar.undo_test values ('aaaaaaaaa');
insert into orastar.undo_test values ('aaaaaaaaaa');
commit;
select dbms_rowid.rowid_relative_fno(rowid) file_id,dbms_rowid.rowid_block_number(rowid) block_id,c1 from orastar.undo_test a;
SQL> select dbms_rowid.rowid_relative_fno(rowid) file_id,dbms_rowid.rowid_block_number(rowid) block_id,c1 from orastar.undo_test a;
FILE_ID BLOCK_ID C1
---------- ---------- ----------
4 175 a
4 175 aa
4 175 aaa
4 175 aaaa
4 175 aaaaa
4 175 aaaaaa
4 175 aaaaaaa
4 175 aaaaaaaa
4 175 aaaaaaaaa
4 175 aaaaaaaaaa
10 rows selected.
SQL>
update orastar.undo_test set c1='b' where c1='a';
set linesize 200
select xidusn,
xidslot,
xidsqn,
ubafil,
ubablk,
ubasqn,
ubarec,
used_ublk,
used_urec
from v$transaction order by 1;
XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC USED_UBLK USED_UREC
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
7 0 1418 6 226 364 1 1 1
update orastar.undo_test set c1='bb' where c1='aa';
XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC USED_UBLK USED_UREC
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
6 0 1803 6 210 335 1 1 1
7 0 1418 6 226 364 1 1 1
SQL>
select segment_name,extent_id,file_id,block_id,blocks from dba_extents where tablespace_name='UNDO2';
SQL> select segment_name,extent_id,file_id,block_id,blocks from dba_extents where tablespace_name='UNDO2';
SEGMENT_NAME EXTENT_ID FILE_ID BLOCK_ID BLOCKS
--------------------------------------------------------------------------------- ---------- ---------- ---------- ----------
_SYSSMU10_312549722$ 0 6 272 8
_SYSSMU10_312549722$ 1 6 280 8
_SYSSMU9_3341729637$ 0 6 256 8
_SYSSMU9_3341729637$ 1 6 264 8
_SYSSMU8_2087434162$ 0 6 240 8
_SYSSMU8_2087434162$ 1 6 248 8
_SYSSMU7_3440425481$ 0 6 224 8 -- dba 6,266
_SYSSMU7_3440425481$ 1 6 232 8
_SYSSMU6_3492297265$ 0 6 208 8 -- dba 6,210
_SYSSMU6_3492297265$ 1 6 216 8
_SYSSMU5_2638362866$ 0 6 192 8
SEGMENT_NAME EXTENT_ID FILE_ID BLOCK_ID BLOCKS
--------------------------------------------------------------------------------- ---------- ---------- ---------- ----------
_SYSSMU5_2638362866$ 1 6 200 8
_SYSSMU4_2087009393$ 0 6 176 8
_SYSSMU4_2087009393$ 1 6 184 8
_SYSSMU3_2503830810$ 0 6 160 8
_SYSSMU3_2503830810$ 1 6 168 8
_SYSSMU2_2772265985$ 0 6 144 8
_SYSSMU2_2772265985$ 1 6 152 8
_SYSSMU1_1958291343$ 0 6 128 8
_SYSSMU1_1958291343$ 1 6 136 8
20 rows selected.
SQL>
### 事务1：a->b
alter session set tracefile_identifier='undo_01';
oradebug setmypid
alter system dump datafile 6 block 226;
oradebug close_trace
oradebug tracefile_name
UNDO BLK:
xid: 0x0007.000.0000058a seq: 0x16c cnt: 0x1 irb: 0x1 icl: 0x0 flg: 0x0000
Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset
---------------------------------------------------------------------------
0x01 0x1f24
*-----------------------------
* Rec #0x1 slt: 0x00 objn: 88329(0x00015909) objd: 88329 tblspc: 4(0x00000004)
* Layer: 11 (Row) opc: 1 rci 0x00
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000Ext idx: 0
flg2: 0
*-----------------------------
uba: 0x00000000.0000.00 ctl max scn: 0x0000.002f881c prv tx scn: 0x0000.002f881c
txn start scn: scn: 0x0000.00000000 logon user: 0
prev brb: 0 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: L itl: xid: 0x0009.000.00000663 uba: 0x01800102.0229.01
flg: C--- lkc: 0 scn: 0x0000.00364733
Array Update of 1 rows:
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 12
ncol: 1 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x010000af hdba: 0x010000aa
itli: 2 ispac: 0 maxfr: 4858
vect = 0
col 0: [10] 61 20 20 20 20 20 20 20 20 20 -- 一条记录: a
End dump data blocks tsn: 20 file#: 6 minblk 226 maxblk 226
### 事务2：aa->bb
alter session set tracefile_identifier='undo_01';
oradebug setmypid
alter system dump datafile 6 block 210;
oradebug close_trace
oradebug tracefile_name
UNDO BLK:
xid: 0x0006.000.0000070b seq: 0x14f cnt: 0x1 irb: 0x1 icl: 0x0 flg: 0x0000
Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset
---------------------------------------------------------------------------
0x01 0x1f24
*-----------------------------
* Rec #0x1 slt: 0x00 objn: 88329(0x00015909) objd: 88329 tblspc: 4(0x00000004)
* Layer: 11 (Row) opc: 1 rci 0x00
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000Ext idx: 0
flg2: 0
*-----------------------------
uba: 0x00000000.0000.00 ctl max scn: 0x0000.002f8882 prv tx scn: 0x0000.002f8882
txn start scn: scn: 0x0000.003642fe logon user: 0
prev brb: 0 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: L itl: xid: 0x0008.000.0000066e uba: 0x018000f2.0125.01
flg: C--- lkc: 0 scn: 0x0000.00364756
Array Update of 1 rows:
tabn: 0 slot: 1(0x1) flag: 0x2c lock: 0 ckix: 234
ncol: 1 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x010000af hdba: 0x010000aa
itli: 1 ispac: 0 maxfr: 4858
vect = 0
col 0: [10] 61 61 20 20 20 20 20 20 20 20 -- 一条记录: aa
create table orastar.undo_test2(c1 char(10),c2 char(10)) tablespace users;
insert into orastar.undo_test2 values ('1','a');
insert into orastar.undo_test2 values ('2','aa');
insert into orastar.undo_test2 values ('3','aaa');
insert into orastar.undo_test2 values ('4','aaaa');
insert into orastar.undo_test2 values ('5','aaaaa');
insert into orastar.undo_test2 values ('6','aaaaaa');
insert into orastar.undo_test2 values ('7','aaaaaaa');
insert into orastar.undo_test2 values ('8','aaaaaaaa');
insert into orastar.undo_test2 values ('9','aaaaaaaaa');
insert into orastar.undo_test2 values ('11','aaaaaaaaaa');
commit;
select dbms_rowid.rowid_relative_fno(rowid) file_id,dbms_rowid.rowid_block_number(rowid) block_id,c1,c2 from orastar.undo_test2 a;
update orastar.undo_test2 set c2='bbb' where c2='aaa';
SQL> set linesize 200
select xidusn,
xidslot,
xidsqn,
ubafil,
ubablk,
ubasqn,
ubarec,
used_ublk,
used_urec
from v$transaction order by 1;SQL> 2 3 4 5 6 7 8 9 10
XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC USED_UBLK USED_UREC
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
4 23 1488 6 1782 519 1 1 1
SQL>
alter session set tracefile_identifier='undo_02';
oradebug setmypid
alter system dump datafile 6 block 1782;
oradebug close_trace
oradebug tracefile_name
SQL> select owner,object_name,object_id from dba_objects where object_name='UNDO_TEST2';
OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_ID
----------
ORASTAR
UNDO_TEST2
88330
SQL>
### 事务3：（3,aaa）->(3,bbb)
UNDO BLK:
xid: 0x0004.017.000005d0 seq: 0x207 cnt: 0x1 irb: 0x1 icl: 0x0 flg: 0x0000
Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset
---------------------------------------------------------------------------
0x01 0x1f3c
*-----------------------------
* Rec #0x1 slt: 0x17 objn: 88330(0x0001590a) objd: 88330 tblspc: 4(0x00000004)
* Layer: 11 (Row) opc: 1 rci 0x00
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000Ext idx: 0
flg2: 0
*-----------------------------
uba: 0x018006e2.0207.35 ctl max scn: 0x0000.0036f451 prv tx scn: 0x0000.0036f482
txn start scn: scn: 0x0000.0037066b logon user: 0
prev brb: 25167573 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: Z
Array Update of 1 rows:
tabn: 0 slot: 2(0x2) flag: 0x2c lock: 0 ckix: 12
ncol: 2 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x010000b3 hdba: 0x010000b2
itli: 2 ispac: 0 maxfr: 4858
vect = 11
col 1: [10] 61 61 61 20 20 20 20 20 20 20 --只记录一列的值
文章转载自数据库技术笔记，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
