当数据库运行一段时间后,随着业务数据的积累,表空间中的表对象会存在不少碎片,这样不仅会影响查询性能,并且会使表对象的占用空间增大,如果长期不处理,会占用巨大的表空间,下面分享一些脚本可以定位碎片率较大的表对象,并且生成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脚本,选择一个适当的业务停机时机分步执行即可。