查询表空间碎片程度：

  • 统计碎片程度：select tablespace_name, count(tablespace_name) from dba_free_space group by tablespace_name having count(tablespace_name) > 10;
  • 合并表空间：alter tablespace HS_USER_DATA coalesce;
  • 释放未使用的表空间：alter table name deallocate unused;

按表名评估碎片程度：

  • 创建视图查看碎片信息：create or replace view ts_blocks_v as select tablespace_name, block_id, bytes, blocks, segment_name from dba_free_space union all select tablespace_name, block_id, bytes, blocks, segment_name from dba_extents;
  • 查看碎片信息：select * from ts_blocks_v;
  • 统计碎片程度：select tablespace_name, sum(bytes), max(bytes), count(block_id) from dba_free_space group by tablespace_name;

按表名查询碎片严重程度：

  • 统计碎片程度：SELECT segment_name table_name, COUNT(*) extents FROM dba_segments WHERE owner NOT IN ('SYS', 'SYSTEM') GROUP BY segment_name HAVING COUNT(*) = (SELECT MAX(COUNT(*)))