概述在生产环境中，经常会遇到因为不断插入数据导致表空间越来越大的情况。由于前期配置问题，没有做分区等优化，实时插入Production数据到表中。删除历史数据以释放空间。因此，DBA一般需要定期对Oracle表进行碎片整理。简单的表整理流程如下：1.找到有碎片的对象，使用以下脚本查看需要整理的对象：--alltables(partition_tables+non_partition_tables)selecta.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_percentfromdba_tablesa,(selectowner,segment_name,sum(bytes/1024/1024)seg_bytes_mbfromdba_segmentsgroupbyowner,segment_name)bwherea.table_name=b.segment_nameanda.owner=b.owneranda.ownernotin('SYS','SYSTEM','OUTLN','DMSYS','TSMSYS','DBSNMP','WMSYS','EXFSYS','CTXSYS','XDB','OLAPSYS','ORDSYS','MDSYS','SYSMAN')anddecode(a.num_rows,0,100,(1-round(a.avg_row_len*a.num_rows/1024/1024/b.seg_bytes_mb,2))*100)>30orderbyb.seg_bytes_mbdesc；2.统计信息检查2.1统计信息检查检查统计采集日期，保证分片查询结果的准确性：selectowner,table_name,last_analyzedfromdba_tablesWhereowner=''ANDtable_name='';2.2Statistics信息收集如果统计信息太旧，重新收集统计信息：execdbms_stats.gather_table_stats(ownname=>'',tabname=>'');3.表碎片整理3.1开行移动altertableenablerowmovement;3.2缩表altertableshrinkspacecascade；3.3无效对象编译语句可能导致引用表的对象（如存储过程、包、视图等）无效运行以下脚本重新编译无效对象。@?/rdbms/admin/utlrp.sql4。查看对象收缩后的结果运行以下脚本，确认对象空间是否已经收缩。--alltables(partition_tables+non_partition_tables)selecta.owner,a.table_name,a.num_rows,a.avg_row_length,a.avg_row_length*a.num_rows/1024/1024.2)real_bytes_MB,round(b.seg_bytes_mb,2);seg_bytes_mb，解码（a.num_rows，0，100，（1-轮（a.avg_row_len*a.num_rows/1024/1024/b.seg_bytes_mb，2））*100）||(bytes/1024/1024)seg_bytes_mbfromdba_segmentsgroupbyowner,segment_name)bwherea.table_name=b.segment_nameanda.owner=b.owneranda.ownernotin('SYS','SYSTEM','OUTLN','DMSYS','TSMSYS','DBSNMP','WMSYS','EXFSYS','CTXSYS','XDB','OLAPSYS','ORDSYS','MDSYS','SYSMAN')anddecode(a.num_rows,0,100,(1-round(a.avg_row_len*;a.num_rows/1024/1024/b.seg_bytes_mb,2))*100)>30orderbyb.seg_bytes_mbdesc;5、性能监控监控数据库会话，是否存在异常等待事件：selectinst_id,sid,serial_event,machine#,sql,module,program,seconds_in_waitfromgv$session;--指定具体位置selectsid,sql_textfromv$sessiona,v$sqlbwheresidin(85,160)and(b.sql_id=a.sql_idorb.sql_id=a.prev_sql_id);;
