[[274251]]

概述

生產(chǎn)環(huán)境中，經(jīng)常會(huì)遇到表由于數(shù)據(jù)不斷插入，導(dǎo)致空間越來(lái)越大，由于前期配置問(wèn)題，沒(méi)有做分區(qū)或者其他優(yōu)化，而且生產(chǎn)數(shù)據(jù)實(shí)時(shí)向表插入。要?jiǎng)h除歷史數(shù)據(jù)來(lái)釋放空間。所以DBA一般都需要定期去對(duì)Oracle表碎片做整理，簡(jiǎn)單整理表碎片整理流程如下：

1、定位存在碎片的對(duì)象

使用如下腳本，檢查需要進(jìn)行碎片整理的對(duì)象：

--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、統(tǒng)計(jì)信息檢查

2.1 統(tǒng)計(jì)信息檢查

查看統(tǒng)計(jì)信息收集日期，確保碎片查詢結(jié)果準(zhǔn)確：

select owner,table_name,last_analyzed from dba_tables Where owner='<OWNER>' AND table_name='<TABLE_NAME>'; 

2.2 統(tǒng)計(jì)信息收集

如果統(tǒng)計(jì)信息過(guò)舊，則重新收集統(tǒng)計(jì)信息：

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

3、表碎片整理

3.1 打開(kāi)行移動(dòng)

alter table <TABLE_NAME> enable row movement ; 

3.2 進(jìn)行表收縮

alter table <TABLE_NAME> shrink space cascade ; 

3.3 失效對(duì)象編譯

語(yǔ)句可能會(huì)造成引用表 的對(duì)象(如存儲(chǔ)過(guò)程、包、視圖等)變?yōu)闊o(wú)效。

運(yùn)行如下腳本，重新編譯失效對(duì)象。

@?/rdbms/admin/utlrp.sql 

4、對(duì)象收縮后的結(jié)果檢查

運(yùn)行如下腳本，確認(rèn)對(duì)象空間是否已經(jīng)完成收縮。

--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、性能監(jiān)控

監(jiān)控?cái)?shù)據(jù)庫(kù)會(huì)話，是否存在異常等待事件：

select inst_id ,sid,serial#,sql_id,event,machine,module,program,seconds_in_wait from gv$session ; 
--看會(huì)話在做什么操作 
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);