从10g开始,oracle开始提供Shrink的命令,假如我们的表空间中支持自动段空间管理 (ASSM),就可以使用这个特性缩小段,即降低HWM。这里需要强调一点,10g的这个新特性,仅对ASSM表空间有效,否则会报 ORA-10635: Invalid segment or tablespace type。
有关ASSM的详细信息,请参考我的Blog:Oracle 自动段空间管理
http://blog.csdn.net/tianlesoftware/archive/2009/12/07/4958989.aspx
如果经常在表上执行DML操作,会造成数据库块中数据分布稀疏,浪费大量空间。同时也会影响全表扫描的性能,因为全表扫描需要访问更多的数据块。从oracle10g开始,表可以通过shrink来重组数据使数据分布更紧密,同时降低HWM释放空闲数据块。
segment shrink分为两个阶段:
1、数据重组(compact):通过一系列insert、delete操作,将数据尽量排列在段的前面。在这个过程中需要在表上加RX锁,即只在需要移动的行上加锁。由于涉及到rowid的改变,需要enable row movement.同时要disable基于rowid的trigger.这一过程对业务影响比较小。
2、HWM调整:第二阶段是调整HWM位置,释放空闲数据块。此过程需要在表上加X锁,会造成表上的所有DML语句阻塞。在业务特别繁忙的系统上可能造成比较大的影响。
shrink space语句两个阶段都执行。
shrink space compact只执行第一个阶段。
如果系统业务比较繁忙,可以先执行shrink space compact重组数据,然后在业务不忙的时候再执行shrink space降低HWM释放空闲数据块。
shrink必须开启行迁移功能。
alter table table_name enable row movement ;
注意:alter table XXX enable row movement语句会造成引用表XXX的对象(如存储过程、包、视图等)变为无效。执行完成后,最好执行一下utlrp.sql来编译无效的对象。
语法:
alter table shrink space [ | compact | cascade ];
alter table shrink space compcat;
收缩表,相当于把块中数据打结实了,但会保持 high water mark;
alter table shrink space;
收缩表,降低 high water mark;
alter table shrink space cascade;
收缩表,降低 high water mark,并且相关索引也要收缩一下下。
alter index idxname shrink space;
回缩索引
1:普通表
Sql脚本,改脚本会生成相应的语句
select'alter table '||table_name||' enable row movement;'||chr(10)||'alter table '||table_name||' shrink space;'||chr(10)from user_tables;
select'alter index '||index_name||' shrink space;'||chr(10)from user_indexes;
2:分区表的处理
进行shrink space时 发生ORA-10631错误.shrink space有一些限制.
在表上建有函数索引(包括全文索引)会失败。
Sql脚本,改脚本会生成相应的语句
select 'alter table '||table_name||' enable row movement;'||chr(10)||'alter table '||table_name||' shrink space;'||chr(10) from user_tables where ;
select 'alter index '||index_name||' shrink space;'||chr(10) from user_indexes where uniqueness='NONUNIQUE' ;
select 'alter table '||segment_name||' modify subpartition '||partition_name||' shrink space;'||chr(10) from user_segments where segment_type='TABLE SUBPARTITION' ';
详细测试:
我们用系统视图all_objects来在上个测试的tablespace ASSM上创建测试表my_objects
/* Formatted on 2009-12-7 20:42:45 (QP5 v5.115.810.9015) */
CREATE TABLESPACE ASSM DATAFILE 'd:\ASSM01.dbf' SIZE 100M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
/* Formatted on 2009-12-7 20:39:26 (QP5 v5.115.810.9015) */
SELECT TABLESPACE_NAME,
BLOCK_SIZE,
EXTENT_MANAGEMENT,
ALLOCATION_TYPE,
SEGMENT_SPACE_MANAGEMENT
FROM dba_tablespaces
WHERE TABLESPACE_NAME = 'ASSM';
TABLESPACE_NAME BLOCK_SIZE EXTENT_MAN ALLOCATIO SEGMEN
--------------------- ---------- ---------- --------- ------
ASSM 8192 LOCAL SYSTEM AUTO
1 row selected.
/* Formatted on 2009-12-7 20:44:15 (QP5 v5.115.810.9015) */
CREATE TABLE my_objects
TABLESPACE assm
AS
SELECT * FROM all_objects;
然后我们随机地从table MY_OBJECTS中删除一部分数据:
SQL> SELECT COUNT ( * ) FROM my_objects;
COUNT(*)
----------
49477
SQL> delete from my_objects where object_name like '%C%';
SQL> delete from my_objects where object_name like '%U%';
SQL> delete from my_objects where object_name like '%A%';
现在我们使用show_space()来看看my_objects的数据存储状况:
注: show_space() 存储过程代码参看一下连接的附件
http://blog.csdn.net/tianlesoftware/archive/2009/12/07/4958989.aspx
SQL>exec show_space('my_objects','auto','T','Y');
Total Blocks............................768
Total Bytes.............................6291456
Unused Blocks...........................68
Unused Bytes............................557056
Last Used Ext FileId....................8
Last Used Ext BlockId...................649
Last Used Block.........................60
*************************************************
The segment is analyzed
0% -- 25% free space blocks.............41
0% -- 25% free space bytes..............335872
25% -- 50% free space blocks............209
25% -- 50% free space bytes.............1712128
50% -- 75% free space blocks............190
50% -- 75% free space bytes.............1556480
75% -- 100% free space blocks...........229
75% -- 100% free space bytes............1875968
Unused Blocks...........................0
Unused Bytes............................0
Total Blocks............................11
Total bytes.............................90112
PL/SQL 过程已成功完成。
这里,table my_objects的HWM下有767个block,其中,free space为25-50%的block有209个,free space为50-75%的block有190个,free space为75-100%的block有229个. Total blocks 11个。
这种情况下,我们需要对这个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','auto','T','Y');
Total Blocks............................272
Total Bytes.............................2228224
Unused Blocks...........................0
Unused Bytes............................0
Last Used Ext FileId....................8
Last Used Ext BlockId...................265
Last Used Block.........................16
*************************************************
The segment is analyzed
0% -- 25% free space blocks.............0
0% -- 25% free space bytes..............0
25% -- 50% free space blocks............0
25% -- 50% free space bytes.............0
50% -- 75% free space blocks............1
50% -- 75% free space bytes.............8192
75% -- 100% free space blocks...........0
75% -- 100% free space bytes............0
Unused Blocks...........................0
Unused Bytes............................0
Total Blocks............................257
Total bytes.............................2105344
在执行玩shrink命令后,我们可以看到,table my_objects的HWM现在降到了271的位置,而且HWM下的block的空间使用状况,Total blocks 的block有257个,free space 为25-50% Block只有0个。
Shrink 的实现机制:
我们接下来讨论一下shrink的实现机制,我们同样使用讨论move机制的那个实验来观察。
/* Formatted on 2009-12-7 20:58:40 (QP5 v5.115.810.9015) */
CREATE TABLE TEST_HWM (id INT, name CHAR (2000))
TABLESPACE ASSM;
INSERT INTO TEST_HWM VALUES (1, 'aa');
INSERT INTO TEST_HWM VALUES (2, 'bb');