oracle碎片整理

生产环境中，经常会遇到表由于数据不断插入，导致空间越来越大，由于前期配置问题，没有做分区或者其他优化，而且生产数据实时向表插入。要删除历史数据来释放空间。所以DBA一般都需要定期去对Oracle表碎片做整理，数据库碎片包含两个方面：
表中碎片化：
使用alter table <table_name> shrink space cascade; 整理表碎片，不会锁表，产生较多归档，
查询可以收缩多少碎片前需要准确统计信息，收缩完成后，会更新dba_tables视图里的BLOCKS值.
检查表空间碎片率FSFI为1%。使用shrink方式整理碎片较高的对象，以释放空间：
alter table <table_name> enable row movement; --打开行移动
alter table <table_name> shrink space cascade; --压缩表及索引并下调HWM（一般使用该参数）
alter table <table_name> shrink space compact; --只压缩不下调HWM
alter table <table_name> shrink space ; --下调HWM，会有阻塞，建议在业务低峰时间段。
alter table <table_name> disable row movement; --关闭行移动
可以使用如下语句找出此表空间下碎片较高的对象（查询前需要确认统计信息的准确性）：

SELECT TABLE_NAME, (BLOCKS * 8192 / 1024 / 1024) - (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024) "Data lower than HWM in MB" 
FROM DBA_TABLES WHERE tablespace_name = 'TEST' order by 2 desc;

使用如下语句resize表空间大小：
–查询可收缩多少

select file_name,ceil((nvl(hwm,1)*(select value from v$parameter where name='db_block_size'))/1024/1024) smallest,
ceil(blocks*(select value from v$parameter where name='db_block_size')/1024/1024) currsize,
ceil(blocks*(select value from v$parameter where name='db_block_size')/1024/1024)-ceil((nvl(hwm,1)*8192)/1024/1024) savings
from dba_data_files a,
(select file_id,max(block_id+blocks+7) hwm from dba_extents where tablespace_name='BAPBUP3'
group by file_id ) b
where a.file_id=b.file_id(+) and tablespace_name='TEST1';

–生成resize数据文件语句

select 'alter database datafile '''||file_name||''' resize ' ||
ceil((nvl(hwm,1)*(select value from v$parameter where name='db_block_size'))/1024/1024)||'m;' cmd
from dba_data_files a,
(select file_id,max(block_id+blocks+7) hwm
from dba_extents where tablespace_name='TEST1'
group by file_id) b
where b.file_id=a.file_id(+)
and ceil(blocks*(select value from v$parameter where name='db_block_size')/1024/1024)-
ceil((nvl(hwm,1)*(select value from v$parameter where name='db_block_size'))/1024/1024)>0;