超詳細(xì)的Oracle數(shù)據(jù)庫(kù)表碎片整理規(guī)范，值得收藏
概述
生產(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='
' AND table_name=' ';
2.2 統(tǒng)計(jì)信息收集
如果統(tǒng)計(jì)信息過(guò)舊，則重新收集統(tǒng)計(jì)信息：
- exec dbms_stats.gather_table_stats(ownname=>'
', tabname =>' ');
3、表碎片整理
3.1 打開行移動(dòng)
- alter table
enable row movement ;
3.2 進(jìn)行表收縮
- alter table
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);
名稱欄目：超詳細(xì)的Oracle數(shù)據(jù)庫(kù)表碎片整理規(guī)范，值得收藏
本文URL：http://www.jiaotiyi.com/article/cdcjosi.html