Oracle:alter table shrink space压缩碎片收缩高水位

以前的技术：

对于oracle的表，数据库在日常使用过程中，不断的insert，delete，update操作，导致表和索引出现碎片是在所难免的事情，碎片多了，sql的执行效率自然就差了，道理很简单，高水位线（HWL）下的许多数据块都是无数据的，但全表扫描的时候要扫描到高水位线的数据块，也就是说oracle要做许多的无用功！在9i的时候，一个很成熟的碎片整理技术。

alter table xxx move    高水位以下合并碎片，不移动高水位
alter table xxx move compress    高水位以下合并碎片，同时压缩表，不移动高水位。

这个move确实整理碎片的效率很高，但是美中不足的是不移动高水位。而且还要重建索引。依稀记得给为了给公司的数据库节省空间（实际上是监控系统发出数据库空间占用过高的警告）。其中几张大表是日报表，数据增删改查比较大，碎片也很多，其中实际占用的空间肯定比高水位的空间小。所以我在剩余的空间下面做：

1.把需要的数据放到一个临时表，create table temp_t1 nologging Select /*+parallel(tab n)*/ * from t1 where .....

2.彻底删了原表，drop table t1 pruge;

3.还原表,ALTER TABLE oldname temp_t1 TO t1 ;

4.重建索引，create index index_name on t1(...);

过程很复杂很耗时间。

因此oracle在10g哦时候提供了shrink space碎片整理功能，不仅能整理碎片还可以收缩高水位,索引也不需要重建。

alter table move 由于不能修改rowid（物理位置的一个id）所以无法降低高水位，而alter table shrink space 是可以修改rowid的所以在使用之前一定要开启行迁移：alter table OM_SERV_SUMMARY_xxxx enable row movement; 

自动过程的思路：

1.先把碎片率高的表找出

select 'drop table ' || segment_name || ' purge;', sum(bytes)/1024/1024 Mbytese from user_segments a , user_tables b
where segment_type='TABLE'  and a.segment_name=b.TABLE_NAME and b.COMPRESSION='DISABLED' 
group by segment_name,COMPRESSION order by sum(bytes)/1024/1024 desc;

安表的空间大小排序找出来，不支持压缩表哦。

先分析：

exec dbms_stats.gather_table_stats(user,'T1',CASCADE=>TRUE); ==这个分析很耗时间可以设定estimate_percent来调整分析的量。

如果与之前的分析时间差距太大也需要重新分析:

select table_name,last_analyzed from user_tables order by last_analyzed desc nulls last; where table_name = 'OM_SERV_SUMMARY_xxxxxx';

SELECT table_name,
       ROUND((blocks * 8/1024), 2) "高水位空间 M",
       ROUND((num_rows * avg_row_len / 1024/1024), 2) "真实使用空间 M",
       ROUND((blocks * 10 / 100) * 8, 2) "预留空间(pctfree) M",
       ROUND((blocks * 8 - (num_rows * avg_row_len / 1024) -blocks * 8 * 10 / 100), 2) "浪费空间 M",
       ((blocks * 8-(num_rows * avg_row_len / 1024))/1024)/(blocks * 8/1024) "浪费空间 %"
  FROM user_tables
 WHERE table_name = 'OM_SERV_SUMMARY_xxxxxx';

在高水位和真实使用的空间之间的差距=浪费空间，而产生浪费空间的原因是高水位的上涨，真实使用的空间变小（大量的delete）而造成的，而这样也会产生大量的碎片。浪费空间 %大于25就需要整理了。

2.开启行迁移

开启之前程序需要记录这个表原来的情况，好在压缩完后回复原来的模样：

select row_movement into vc_movement from user_tables where table_name='OM_SERV_SUMMARY_xxxxx';

rowid物化视图必须在压缩操作之后重建所以需要开启行迁移：

alter table OM_SERV_SUMMARY_xxxxx enable row movement; 

3.进行压缩

alter table OM_SERV_SUMMARY_xxxxx shrink space cascade;

连同索引一起压缩，比move方便多。

再根据实际情况关闭行迁移

alter table OM_SERV_SUMMARY_xxxxx disable row movement; 

查看表段空间下降许多

 select sum(bytes)/1024/1024 from user_segments where segment_name='OM_SERV_SUMMARY_xxxxx';

不过在批量实践当中，在分析的过程上面耗上很多的资源，所以实践当中可以根据自己的业务逻辑来分析这一张表的碎片情况，技术和业务的结合，效率更佳。