Oracle表碎片释放方案【定位碎片大表并生成move脚本】

当数据库运行一段时间后，随着业务数据的积累，表空间中的表对象会存在不少碎片，这样不仅会影响查询性能，并且会使表对象的占用空间增大，如果长期不处理，会占用巨大的表空间，下面分享一些脚本可以定位碎片率较大的表对象，并且生成MOVE脚本，方便DBA进行表迁移处理，以释放碎片空间。

首先，先统计某表空间下，表数据实际占用空间与表分配空间之差（MB），这里统计出差异大于1000MB的表对象，并且列出数据占用大小，表分配大小，差异额等明细数据：

Select Num_Rows,
       Avg_Row_Len * Num_Rows / 1024 / 1024 / 0.9 数据实际占用大小,
       Blocks * 8 / 1024 表分配大小,
       (Blocks * 8 / 1024 - Avg_Row_Len * Num_Rows / 1024 / 1024 / 0.9) 差别,
       Table_Name
  From Dba_Tables
 Where Tablespace_Name = 'OMS_DATA'
 And blocks * 8 / 1024 - Avg_Row_Len * Num_Rows / 1024 / 1024 / 0.9 > 1000
  Order by 差别 Desc;

–参考以上思路，生成表对象MOVE脚本：

SELECT NUM_ROWS,
       ROUND(AVG_ROW_LEN * NUM_ROWS / 1024 / 1024 / 0.9) real,
       ROUND(BLOCKS * 8 / 1024) allcate,
       ROUND(BLOCKS * 8 / 1024 -
             AVG_ROW_LEN * NUM_ROWS / 1024 / 1024 / 0.9) dif,
       TABLE_NAME,
       'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' MOVE;'
  FROM DBA_TABLES
 WHERE OWNER IN ('ODS', 'EDS', 'DM')
   AND (BLOCKS * 8 / 1024 - AVG_ROW_LEN * NUM_ROWS / 1024 / 1024 / 0.9) > 1000
 ORDER BY dif DESC;

注意：以上生成move脚本的前提是，对当前库所有表进行表统计信息更新，这样生成的结果才较准确，因为以上视图是根据统计信息中的行数与块数等信息进行评估的！最后将生成的MOVE脚本，选择一个适当的业务停机时机分步执行即可。