### 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   --只記錄一列的值
```

oracle undo分配規則

相關文章