undo表空间分配给dml的测试-CSDN博客

测试：开并行update ,测试回滚段的工作方式。

当执行下面的语句的时候，报下面的错误。

sys@DWLAB>update xcy_test.xcy_fh_test set name='kkkkkk' where dbms_rowid.rowid_block_number(rowid)=351;

update xcy_test.xcy_fh_test set name='kkkkkk' where dbms_rowid.rowid_block_number(rowid)=351

ERROR at line 1:

ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS6'

（该session 占有回滚段74，占用8 个block, 想再扩张8个block 的时候报错，已经没有剩余空间可供扩张）。

此时：回滚段表空间有17个回滚段，只使用了四个，编号为59，65，70，74，

其余的12个并没有回收，虽然没有事物占用，但没有回收起来给该update 使用。

（在后面的测试中发现，是可以回收的，在这里没有回收，是因为这些没有被使用的segment

都是128k,回滚段的最小大小是128k).

sys@DWLAB>select xidusn,xidslot,ubafil,ubablk,start_ubafil,start_ubablk,sid ,USED_UBLK , seg.header_block,seg.bytes/1024 from

2 v$transaction ,v$session,v$rollname r, dba_segments seg where addr=taddr

3 and xidusn=r.usn and r.name=seg.segment_name order by xidusn,ubablk;

XIDUSN XIDSLOT UBAFIL UBABLK START_UBAFIL START_UBABLK SID USED_UBLK HEADER_BLOCK SEG.BYTES/1024

---------- ---------- ---------- ---------- ------------ ------------ ---------- ---------- ------------ --------------

59 0 3 130 3 130 76 1 128 128

65 1 3 226 3 226 13 1 224 128

70 1 3 306 3 306 197 1 304 128

74 1 3 377 3 370 139 8 368 128

上面的查询得到被占用的回滚段。

sys@DWLAB>select * from v$rollname;

USN NAME

---------- ------------------------------------------------------------

0 SYSTEM

59 _SYSSMU59_1411397850$

60 _SYSSMU60_429352262$

61 _SYSSMU61_3153439023$

62 _SYSSMU62_2341634032$

63 _SYSSMU63_3856984295$

64 _SYSSMU64_415199402$

65 _SYSSMU65_67136245$

66 _SYSSMU66_1138725751$

67 _SYSSMU67_439764099$

68 _SYSSMU68_1962979338$

69 _SYSSMU69_288275769$

70 _SYSSMU70_360228070$

71 _SYSSMU71_1144283645$

72 _SYSSMU72_3544873686$

73 _SYSSMU73_1132334743$

74 _SYSSMU74_1575303993$

所有的回滚段。

现在，我们开另外一个窗口，

执行同样的语句：

sys@DWLAB>update xcy_test.xcy_fh_test set name='kkkkkk' where dbms_rowid.rowid_block_number(rowid)=351;

484 rows updated.

Update 成功。

执行下面的sql ，查看一下回滚段的占用情况：

sys@DWLAB>select xidusn,xidslot,ubafil,ubablk,start_ubafil,start_ubablk,sid ,USED_UBLK , seg.header_block,seg.bytes/1024 from

2 v$transaction ,v$session,v$rollname r, dba_segments seg where addr=taddr

3 and xidusn=r.usn and r.name=seg.segment_name order by xidusn,ubablk;

XIDUSN XIDSLOT UBAFIL UBABLK START_UBAFIL START_UBABLK SID USED_UBLK HEADER_BLOCK SEG.BYTES/1024

---------- ---------- ---------- ---------- ------------ ------------ ---------- ---------- ------------ --------------

59 0 3 130 3 130 76 1 128 128

60 4 3 153 3 146 200 7 144 128

65 1 3 226 3 226 13 1 224 128

70 1 3 306 3 306 197 1 304 128

74 1 3 377 3 370 139 8 368 128

发现该session 使用了没有利用的回滚段60，语句得以执行成功。

继续开其他session 进行update , 将所有的undo segment 都利用完：

sys@DWLAB>/

XIDUSN XIDSLOT UBAFIL UBABLK START_UBAFIL START_UBABLK SID USED_UBLK HEADER_BLOCK SEG.BYTES/1024

---------- ---------- ---------- ---------- ------------ ------------ ---------- ---------- ------------ --------------

59 0 3 130 3 130 76 1 128 128

60 4 3 153 3 146 200 7 144 128

61 9 3 162 3 162 80 1 160 128

62 10 3 178 3 178 202 1 176 128

63 11 3 194 3 194 16 1 192 128

64 14 3 210 3 210 79 1 208 128

65 1 3 226 3 226 13 1 224 128

66 9 3 242 3 242 18 1 240 128

67 10 3 258 3 258 78 1 256 128

68 15 3 274 3 274 143 1 272 128

69 14 3 291 3 291 17 1 288 128

70 1 3 306 3 306 197 1 304 128

71 9 3 322 3 322 201 1 320 128

72 10 3 338 3 338 141 1 336 128

73 12 3 354 3 354 142 1 352 128

74 1 3 377 3 370 139 8 368 128

16 rows selected.

在进行并行update , 则出现两个session 共用undo segment 的情况。

此时，将 sid 为139 的session 进行提交。

开另外一个session 进行update .

XIDUSN XIDSLOT UBAFIL UBABLK START_UBAFIL START_UBABLK SID USED_UBLK HEADER_BLOCK SEG.BYTES/1024

---------- ---------- ---------- ---------- ------------ ------------ ---------- ---------- ------------ --------------

59 0 3 130 3 130 76 1 128 128

60 4 3 153 3 146 200 7 144 128

61 9 3 162 3 162 80 1 160 128

62 10 3 178 3 178 202 1 176 128

63 11 3 194 3 194 16 1 192 128

64 14 3 210 3 210 79 1 208 128

65 1 3 226 3 226 13 1 224 128

65 10 3 227 3 227 144 1 224 128

66 9 3 242 3 242 18 1 240 128

67 10 3 258 3 258 78 1 256 128

68 15 3 274 3 274 143 1 272 128

69 14 3 291 3 291 17 1 288 128

70 1 3 306 3 306 197 1 304 128

71 9 3 322 3 322 201 1 320 128

72 10 3 338 3 338 141 1 336 128

73 12 3 354 3 354 142 1 352 128

新的session 使用了刚才提交事务的回滚段。

我们继续执行update , 看到回滚段的情况如下：

其中73 号回滚段被sid 为142，145 的session 共同占有，60号回滚段被sid 为200的session

占有，该回滚段的大小为256k, 被占用的block 是24，剩余的可以使用的undo block 已经不多。在该session 上继续执行update .

sys@DWLAB>select sys_context('userenv','sid') from dual;

SYS_CONTEXT('USERENV','SID')

------------------------------------------------------------------------------------------------------------------------------------------------------

200

sys@DWLAB>update xcy_test.xcy_fh_test set name='kkkkkk' where dbms_rowid.rowid_block_number(rowid) in (516);

update xcy_test.xcy_fh_test set name='kkkkkk' where dbms_rowid.rowid_block_number(rowid) in (516)

ERROR at line 1:

ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS6'

报错，已经没有办法扩张8个block .

继续测试：

如果其他session 产生少量undo ,却占用的一个比较大的回滚段，当其他session 的回滚段不足的时候，是否会回收其多占用的空间？

上面实验中sid 为145 的session 占用了52个undo block ,我们先将其进行提交。

提交之后，我们发现73号的回滚段已经只被sid 为142 的session 占有，但只占有1个块，但该回滚段的大小为：576K.

此时：再执行上面出错的语句，看是否会报错。

update xcy_test.xcy_fh_test set name='kkkkkk' where dbms_rowid.rowid_block_number(rowid) in (516);

sys@DWLAB>/

update xcy_test.xcy_fh_test set name='kkkkkk' where dbms_rowid.rowid_block_number(rowid) in (516)

ERROR at line 1:

ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS6'

仍然报错，尝试执行update 一条记录。

sys@DWLAB>update xcy_test.xcy_fh_test set name='xcjjjx' where rowid in ('AAASZbAAGAAAADKAEe');

update xcy_test.xcy_fh_test set name='xcjjjx' where rowid in ('AAASZbAAGAAAADKAEe')

ERROR at line 1:

ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS6'

还是报错。

所以，没有把其他的session 所占的多余的回滚段收回（如果真是这样，就可能存在某些问题。）

另外，我们看到74号回滚段仅仅被一个session 所占有，我们将占有该回滚段的session提交，测试这些空间是否会被其他session 所利用。我们看到，74 号回滚段不再被其他

Session 占用。

继续执行上面的update :

sys@DWLAB>update xcy_test.xcy_fh_test set name='kkkkkk' where dbms_rowid.rowid_block_number(rowid) in (516);

484 rows updated.

看到， update 已经成功。再来看看回滚段的站有情况。

Sid 为200的session 所占的回滚段的大小由256K扩展到320K。（扩张了64K,8个block）.

再来看看usn 为74 的回滚段的大小。

大小已经改变。从原来的384K 减到320K. 减少了46K.

再起一个session :

sys@DWLAB>select sys_context('userenv','sid') from dual;

SYS_CONTEXT('USERENV','SID')

-------------------------------------------------------------------------------------------------------------------

sys@DWLAB>update xcy_test.xcy_fh_test set name='xcjjjx' where rowid in ('AAASZbAAGAAAADKAEe');

1 row updated.

再来查看回滚段的使用情况：

可以看到74 号回滚段的被新的session 使用。

为了再次测试被session 占有的回滚段是否会收缩。其他session 在进行update 测试：

这次选择sid 为81 的session.

sys@DWLAB>select sys_context('userenv','sid') from dual;

SYS_CONTEXT('USERENV','SID')

-----------------------------------------------------------------------------------------------------

sys@DWLAB>update xcy_test.xcy_fh_test set name='kkkkkk' where dbms_rowid.rowid_block_number(rowid) in (366);

484 rows updated.

成功update .

（执行update 之前，sid 81 session 占用undo segment 67 , 回滚段大小是128K）

执行之后，占用的回滚段情况为：

Sid 为81 的session 所占的67 号回滚段扩张了64K , sid 为74 的session 所占的回滚段缩小了64k( 由320K 降至256K).

为什么73号回滚段有足够多的剩余空间，却一直没有回收？

（难道是因为开始有两个session 占用了同一个回滚段，另外一个session 虽然提交了，但是占用的回滚段却没有办法回收？）

测试回滚段得出的结论：

1. 如果undo 的表空间存在没有被使用的undo segment , 则优先使用已存在的undo segment.

2. 在空间可以扩张的情况下，如果所有的回滚段都有session 占有。Oracle 会为session 起一个新的undo segment 供新的session 进行操作。

3. 假如session 所占的回滚段的剩余空间不足，oracle 会对segment 进行extent拓展，如果表空间的剩余空间不足以支撑扩张的大小，oracle 会去收缩不活跃的回滚段，如果不存在不活跃的回滚段，则同样会去检测其他活跃的回滚段是否可以收缩，如果可以，则将从其他回滚段收缩的空间链接到自身的回滚段中，完成回滚段扩张。否则，报错。