深入探讨Oracle数据库10g的Shrink机制
时间：2010-11-16 来源：kuncomputer
从10g开始，oracle开始提供Shrink的命令，假如我们的表空间中支持自动段空间管理 (ASSM),就可以使用这个特性缩小段，即降低HWM。这里需要强调一点，10g的这个新特性，仅对ASSM表空间有效，否则会报 ORA-10635: Invalid segment or tablespace type。
在这里，我们来讨论如和对一个ASSM的segment回收浪费的空间。
同样，我们用系统视图all_objects来在tablespace ASSM上创建测试表my_objects，这一小节的内容，实验环境为oracle10.1.0.2：
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod
PL/SQL Release 10.1.0.2.0 - Production
CORE 10.1.0.2.0 Production
TNS for 32-bit Windows: Version 10.1.0.2.0 - Production
NLSRTL Version 10.1.0.2.0 – Production
SQL> select TABLESPACE_NAME,BLOCK_SIZE,EXTENT_MANAGEMENT,
2 ALLOCATION_TYPE, SEGMENT_SPACE_MANAGEMENT
3 from dba_tablespaces where TABLESPACE_NAME = 'ASSM';
TABLESPACE_NAME BLOCK_SIZE EXTENT_MANAGEMENT ALLOCATION_TYPE SEGMENT_SPACE_MANAGEMENT
---------------- ---------- ----------------- --------------- ------------------------
ASSM 8192 LOCAL UNIFORM AUTO
SQL> create table my_objects tablespace assm
2 as select * from all_objects;
Table created
然后我们随机地从table MY_OBJECTS中删除一部分数据：
SQL> select count(*) from my_objects;
COUNT(*)
----------
47828
SQL> delete from my_objects where object_name like '%C%';
16950 rows deleted
SQL> delete from my_objects where object_name like '%U%';
4503 rows deleted
SQL> delete from my_objects where object_name like '%A%';
6739 rows deleted
现在我们使用show_space和show_space_assm来看看my_objects的数据存储状况：
SQL> exec show_space('MY_OBJECTS','DLINGER');
Total Blocks............................680
Total Bytes.............................5570560
Unused Blocks...........................1
Unused Bytes............................8192
Last Used Ext FileId....................6
Last Used Ext BlockId...................793
Last Used Block.........................4
PL/SQL 过程已成功完成。
SQL> exec show_space_assm('MY_OBJECTS','DLINGER');
free space 0-25% Blocks:................0
free space 25-50% Blocks:...............205
free space 50-75% Blocks:...............180
free space 75-100% Blocks:..............229
Full Blocks:............................45
Unformatted blocks:.....................0
PL/SQL 过程已成功完成。
这里，table my_objects的HWM下有679个block，其中，free space为25-50%的block有205个，free space为50-75%的block有180个，free space为75-100%的block有229个，full space的block只有45个，这种情况下，我们需要对这个table的现有数据行进行重组。
要使用assm上的shink，首先我们需要使该表支持行移动，可以用这样的命令来完成：
alter table my_objects enable row movement;
现在，就可以来降低my_objects的HWM，回收空间了，使用命令：
alter table bookings shrink space;
我们具体的看一下实验的结果：
SQL> alter table my_objects enable row movement;
表已更改。
SQL> alter table my_objects shrink space;
表已更改。
SQL> exec show_space('MY_OBJECTS','DLINGER');
Total Blocks............................265
Total Bytes.............................2170880
Unused Blocks...........................2
Unused Bytes............................16384
Last Used Ext FileId....................6
Last Used Ext BlockId...................308
Last Used Block.........................3
PL/SQL 过程已成功完成。
SQL> exec show_space_assm('MY_OBJECTS','DLINGER');
free space 0-25% Blocks:................0
free space 25-50% Blocks:...............1
free space 50-75% Blocks:...............0
free space 75-100% Blocks:..............0
Full Blocks:............................249
Unformatted blocks:.....................0
PL/SQL 过程已成功完成。
在执行玩shrink命令后，我们可以看到，table my_objects的HWM现在降到了264的位置，而且HWM下的block的空间使用状况，full space的block有249个，free space 为25-50% Block只有1个。
我们接下来讨论一下shrink的实现机制，我们同样使用讨论move机制的那个实验来观察。
SQL> create table TEST_HWM (id int ,name char(2000)) tablespace ASSM;
Table created
往table test_hwm中插入如下的数据：
insert into TEST_HWM values (1,'aa');
insert into TEST_HWM values (2,'bb');
insert into TEST_HWM values (2,'cc');
insert into TEST_HWM values (3,'dd');
insert into TEST_HWM values (4,'ds');
insert into TEST_HWM values (5,'dss');
insert into TEST_HWM values (6,'dss');
insert into TEST_HWM values (7,'ess');
insert into TEST_HWM values (8,'es');
insert into TEST_HWM values (9,'es');
insert into TEST_HWM values (10,'es');
我们来看看这个table的rowid和block的ID和信息：
SQL> select rowid , id,name from TEST_HWM;
ROWID ID NAME
------------------ ---------- ----- ---------
AAANhqAAGAAAAFHAAA 1 aa
AAANhqAAGAAAAFHAAB 2 bb
AAANhqAAGAAAAFHAAC 2 cc
AAANhqAAGAAAAFIAAA 3 dd
AAANhqAAGAAAAFIAAB 4 ds
AAANhqAAGAAAAFIAAC 5 dss
AAANhqAAGAAAAFJAAA 6 dss
AAANhqAAGAAAAFJAAB 7 ess
AAANhqAAGAAAAFJAAC 8 es
AAANhqAAGAAAAFKAAA 9 es
AAANhqAAGAAAAFKAAB 10 es
11 rows selected
SQL> select EXTENT_ID,FILE_ID,RELATIVE_FNO,BLOCK_ID,BLOCKS
2 from dba_extents where segment_name='TEST_HWM' ;
EXTENT_ID FILE_ID RELATIVE_FNO BLOCK_ID BLOCKS
---------- ---------- ------------ ---------- ----------
0 6 6 324 5
1 6 6 329 5
然后从table test_hwm中删除一些数据：
delete from TEST_HWM where id = 2; delete from TEST_HWM where id = 4; delete from TEST_HWM where id = 3; delete from TEST_HWM where id = 7; delete from TEST_HWM where id = 8;
观察table test_hwm的rowid和blockid的信息：
SQL> select rowid , id,name from TEST_HWM;
ROWID ID NAME
------------------ ---------- ----- --------
AAANhqAAGAAAAFHAAA 1 aa
AAANhqAAGAAAAFIAAC 5 dss
AAANhqAAGAAAAFJAAA 6 dss
AAANhqAAGAAAAFKAAA 9 es
AAANhqAAGAAAAFKAAB 10 es
SQL> select EXTENT_ID,FILE_ID,RELATIVE_FNO,BLOCK_ID,BLOCKS
2 from dba_extents where segment_name='TEST_HWM' ;
EXTENT_ID FILE_ID RELATIVE_FNO BLOCK_ID BLOCKS
在这里，我们来讨论如和对一个ASSM的segment回收浪费的空间。
同样，我们用系统视图all_objects来在tablespace ASSM上创建测试表my_objects，这一小节的内容，实验环境为oracle10.1.0.2：
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod
PL/SQL Release 10.1.0.2.0 - Production
CORE 10.1.0.2.0 Production
TNS for 32-bit Windows: Version 10.1.0.2.0 - Production
NLSRTL Version 10.1.0.2.0 – Production
SQL> select TABLESPACE_NAME,BLOCK_SIZE,EXTENT_MANAGEMENT,
2 ALLOCATION_TYPE, SEGMENT_SPACE_MANAGEMENT
3 from dba_tablespaces where TABLESPACE_NAME = 'ASSM';
TABLESPACE_NAME BLOCK_SIZE EXTENT_MANAGEMENT ALLOCATION_TYPE SEGMENT_SPACE_MANAGEMENT
---------------- ---------- ----------------- --------------- ------------------------
ASSM 8192 LOCAL UNIFORM AUTO
SQL> create table my_objects tablespace assm
2 as select * from all_objects;
Table created
然后我们随机地从table MY_OBJECTS中删除一部分数据：
SQL> select count(*) from my_objects;
COUNT(*)
----------
47828
SQL> delete from my_objects where object_name like '%C%';
16950 rows deleted
SQL> delete from my_objects where object_name like '%U%';
4503 rows deleted
SQL> delete from my_objects where object_name like '%A%';
6739 rows deleted
现在我们使用show_space和show_space_assm来看看my_objects的数据存储状况：
SQL> exec show_space('MY_OBJECTS','DLINGER');
Total Blocks............................680
Total Bytes.............................5570560
Unused Blocks...........................1
Unused Bytes............................8192
Last Used Ext FileId....................6
Last Used Ext BlockId...................793
Last Used Block.........................4
PL/SQL 过程已成功完成。
SQL> exec show_space_assm('MY_OBJECTS','DLINGER');
free space 0-25% Blocks:................0
free space 25-50% Blocks:...............205
free space 50-75% Blocks:...............180
free space 75-100% Blocks:..............229
Full Blocks:............................45
Unformatted blocks:.....................0
PL/SQL 过程已成功完成。
这里，table my_objects的HWM下有679个block，其中，free space为25-50%的block有205个，free space为50-75%的block有180个，free space为75-100%的block有229个，full space的block只有45个，这种情况下，我们需要对这个table的现有数据行进行重组。
要使用assm上的shink，首先我们需要使该表支持行移动，可以用这样的命令来完成：
alter table my_objects enable row movement;
现在，就可以来降低my_objects的HWM，回收空间了，使用命令：
alter table bookings shrink space;
我们具体的看一下实验的结果：
SQL> alter table my_objects enable row movement;
表已更改。
SQL> alter table my_objects shrink space;
表已更改。
SQL> exec show_space('MY_OBJECTS','DLINGER');
Total Blocks............................265
Total Bytes.............................2170880
Unused Blocks...........................2
Unused Bytes............................16384
Last Used Ext FileId....................6
Last Used Ext BlockId...................308
Last Used Block.........................3
PL/SQL 过程已成功完成。
SQL> exec show_space_assm('MY_OBJECTS','DLINGER');
free space 0-25% Blocks:................0
free space 25-50% Blocks:...............1
free space 50-75% Blocks:...............0
free space 75-100% Blocks:..............0
Full Blocks:............................249
Unformatted blocks:.....................0
PL/SQL 过程已成功完成。
在执行玩shrink命令后，我们可以看到，table my_objects的HWM现在降到了264的位置，而且HWM下的block的空间使用状况，full space的block有249个，free space 为25-50% Block只有1个。
我们接下来讨论一下shrink的实现机制，我们同样使用讨论move机制的那个实验来观察。
SQL> create table TEST_HWM (id int ,name char(2000)) tablespace ASSM;
Table created
往table test_hwm中插入如下的数据：
insert into TEST_HWM values (1,'aa');
insert into TEST_HWM values (2,'bb');
insert into TEST_HWM values (2,'cc');
insert into TEST_HWM values (3,'dd');
insert into TEST_HWM values (4,'ds');
insert into TEST_HWM values (5,'dss');
insert into TEST_HWM values (6,'dss');
insert into TEST_HWM values (7,'ess');
insert into TEST_HWM values (8,'es');
insert into TEST_HWM values (9,'es');
insert into TEST_HWM values (10,'es');
我们来看看这个table的rowid和block的ID和信息：
SQL> select rowid , id,name from TEST_HWM;
ROWID ID NAME
------------------ ---------- ----- ---------
AAANhqAAGAAAAFHAAA 1 aa
AAANhqAAGAAAAFHAAB 2 bb
AAANhqAAGAAAAFHAAC 2 cc
AAANhqAAGAAAAFIAAA 3 dd
AAANhqAAGAAAAFIAAB 4 ds
AAANhqAAGAAAAFIAAC 5 dss
AAANhqAAGAAAAFJAAA 6 dss
AAANhqAAGAAAAFJAAB 7 ess
AAANhqAAGAAAAFJAAC 8 es
AAANhqAAGAAAAFKAAA 9 es
AAANhqAAGAAAAFKAAB 10 es
11 rows selected
SQL> select EXTENT_ID,FILE_ID,RELATIVE_FNO,BLOCK_ID,BLOCKS
2 from dba_extents where segment_name='TEST_HWM' ;
EXTENT_ID FILE_ID RELATIVE_FNO BLOCK_ID BLOCKS
---------- ---------- ------------ ---------- ----------
0 6 6 324 5
1 6 6 329 5
然后从table test_hwm中删除一些数据：
delete from TEST_HWM where id = 2; delete from TEST_HWM where id = 4; delete from TEST_HWM where id = 3; delete from TEST_HWM where id = 7; delete from TEST_HWM where id = 8;
观察table test_hwm的rowid和blockid的信息：
SQL> select rowid , id,name from TEST_HWM;
ROWID ID NAME
------------------ ---------- ----- --------
AAANhqAAGAAAAFHAAA 1 aa
AAANhqAAGAAAAFIAAC 5 dss
AAANhqAAGAAAAFJAAA 6 dss
AAANhqAAGAAAAFKAAA 9 es
AAANhqAAGAAAAFKAAB 10 es
SQL> select EXTENT_ID,FILE_ID,RELATIVE_FNO,BLOCK_ID,BLOCKS
2 from dba_extents where segment_name='TEST_HWM' ;
EXTENT_ID FILE_ID RELATIVE_FNO BLOCK_ID BLOCKS
相关阅读 更多 +
