### 1.问题描述 ```sql 当A会话修改一个块第一行数据，该块有10行，提交后B会话修改第二行， 那么按块结构undo中应存在两个undo块，每个块包含20行数据，而不是一个undo块，保存A和B会话修改两行数据的旧值? ``` ### 2.实验结论 ```sql 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 --只记录一列的值 ```