超详细的Oracle数据库表碎片整理规范，值得收藏

概述

生产环境中，经常会遇到表由于数据不断插入，导致空间越来越大，由于前期配置问题，没有做分区或者其他优化，而且生产数据实时向表插入。要删除历史数据来释放空间。所以DBA一般都需要定期去对Oracle表碎片做整理，简单整理表碎片整理流程如下：

1、定位存在碎片的对象

使用如下脚本，检查需要进行碎片整理的对象：

 
 
 
 

  
  
  
  
--all tables(partition_tables + non_partition_tables ) 
  
  
  
  
select a.owner, 
  
  
  
  
 a.table_name, 
  
  
  
  
 a.num_rows, 
  
  
  
  
 a.avg_row_len, 
  
  
  
  
 round(a.avg_row_len * a.num_rows / 1024 / 1024, 2) real_bytes_MB, 
  
  
  
  
 round(b.seg_bytes_mb, 2) seg_bytes_mb, 
  
  
  
  
 decode(a.num_rows, 
  
  
  
  
 0, 
  
  
  
  
 100, 
  
  
  
  
 (1 - round(a.avg_row_len * a.num_rows / 1024 / 1024 / 
  
  
  
  
 b.seg_bytes_mb, 
  
  
  
  
 2)) * 100) || '%' frag_percent 
  
  
  
  
 from dba_tables a, 
  
  
  
  
 (select owner, segment_name, sum(bytes / 1024 / 1024) seg_bytes_mb 
  
  
  
  
 from dba_segments 
  
  
  
  
 group by owner, segment_name) b 
  
  
  
  
 where a.table_name = b.segment_name 
  
  
  
  
 and a.owner = b.owner 
  
  
  
  
 and a.owner not in 
  
  
  
  
 ('SYS', 'SYSTEM', 'OUTLN', 'DMSYS', 'TSMSYS', 'DBSNMP', 'WMSYS', 
  
  
  
  
 'EXFSYS', 'CTXSYS', 'XDB', 'OLAPSYS', 'ORDSYS', 'MDSYS', 'SYSMAN') 
  
  
  
  
 and decode(a.num_rows, 
  
  
  
  
 0, 
  
  
  
  
 100, 
  
  
  
  
 (1 - round(a.avg_row_len * a.num_rows / 1024 / 1024 / 
  
  
  
  
 b.seg_bytes_mb, 
  
  
  
  
 2)) * 100) > 30 
  
  
  
  
 order by b.seg_bytes_mb desc; 
 
 
 
 

2、统计信息检查

2.1 统计信息检查

查看统计信息收集日期，确保碎片查询结果准确：

 
 
 
 

  
  
  
  
select owner,table_name,last_analyzed from dba_tables Where owner='' AND table_name=''; 
 
 
 
 

2.2 统计信息收集

如果统计信息过旧，则重新收集统计信息：

 
 
 
 

  
  
  
  
exec dbms_stats.gather_table_stats(ownname=>'', tabname =>''); 
 
 
 
 

3、表碎片整理

3.1 打开行移动

 
 
 
 

  
  
  
  
alter table  enable row movement ; 
 
 
 
 

3.2 进行表收缩

 
 
 
 

  
  
  
  
alter table  shrink space cascade ; 
 
 
 
 

3.3 失效对象编译

语句可能会造成引用表 的对象(如存储过程、包、视图等)变为无效。

运行如下脚本，重新编译失效对象。

 
 
 
 

  
  
  
  
@?/rdbms/admin/utlrp.sql 
 
 
 
 

4、对象收缩后的结果检查

运行如下脚本，确认对象空间是否已经完成收缩。

 
 
 
 

  
  
  
  
--all tables(partition_tables + non_partition_tables ) 
  
  
  
  
select a.owner, 
  
  
  
  
 a.table_name, 
  
  
  
  
 a.num_rows, 
  
  
  
  
 a.avg_row_len, 
  
  
  
  
 round(a.avg_row_len * a.num_rows / 1024 / 1024, 2) real_bytes_MB, 
  
  
  
  
 round(b.seg_bytes_mb, 2) seg_bytes_mb, 
  
  
  
  
 decode(a.num_rows, 
  
  
  
  
 0, 
  
  
  
  
 100, 
  
  
  
  
 (1 - round(a.avg_row_len * a.num_rows / 1024 / 1024 / 
  
  
  
  
 b.seg_bytes_mb, 
  
  
  
  
 2)) * 100) || '%' frag_percent 
  
  
  
  
 from dba_tables a, 
  
  
  
  
 (select owner, segment_name, sum(bytes / 1024 / 1024) seg_bytes_mb 
  
  
  
  
 from dba_segments 
  
  
  
  
 group by owner, segment_name) b 
  
  
  
  
 where a.table_name = b.segment_name 
  
  
  
  
 and a.owner = b.owner 
  
  
  
  
 and a.owner not in 
  
  
  
  
 ('SYS', 'SYSTEM', 'OUTLN', 'DMSYS', 'TSMSYS', 'DBSNMP', 'WMSYS', 
  
  
  
  
 'EXFSYS', 'CTXSYS', 'XDB', 'OLAPSYS', 'ORDSYS', 'MDSYS', 'SYSMAN') 
  
  
  
  
 and decode(a.num_rows, 
  
  
  
  
 0, 
  
  
  
  
 100, 
  
  
  
  
 (1 - round(a.avg_row_len * a.num_rows / 1024 / 1024 / 
  
  
  
  
 b.seg_bytes_mb, 
  
  
  
  
 2)) * 100) > 30 
  
  
  
  
 order by b.seg_bytes_mb desc; 
 
 
 
 

5、性能监控

监控数据库会话，是否存在异常等待事件：

 
 
 
 

  
  
  
  
select inst_id ,sid,serial#,sql_id,event,machine,module,program,seconds_in_wait from gv$session ; 
  
  
  
  
--看会话在做什么操作 
  
  
  
  
select sid, sql_text 
  
  
  
  
 from v$session a, v$sql b 
  
  
  
  
 where sid in(85,160) 
  
  
  
  
 and(b.sql_id = a.sql_id or b.sql_id = a.prev_sql_id); 
 
 
 
 

网页标题：超详细的Oracle数据库表碎片整理规范，值得收藏
本文地址：http://cdxtjz.cn/article/cdcjosi.html