Resize Oracle Datafile的脚本

转自惜总文章:http://ask.orasos.com/thread-11-1-1.html

set verify offcolumn file_name format a50 word_wrappedcolumn smallest format 999,990 heading "Smallest|Size|Poss."column currsize format 999,990 heading "Current|Size"column savings  format 999,990 heading "Poss.|Savings"break on reportcompute sum of savings on report column value new_val blksizeselect value from v$parameter where name = 'db_block_size'/  select file_name,       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,       ceil( blocks*&&blksize/1024/1024) currsize,       ceil( blocks*&&blksize/1024/1024) -       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savingsfrom dba_data_files a,     ( select file_id, max(block_id+blocks-1) hwm         from dba_extents        group by file_id ) bwhere a.file_id = b.file_id(+)/  column cmd format a75 word_wrapped select 'alter database datafile '''||file_name||''' resize ' ||       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 )  || 'm;' cmdfrom dba_data_files a,     ( select file_id, max(block_id+blocks-1) hwm         from dba_extents        group by file_id ) bwhere a.file_id = b.file_id(+)  and ceil( blocks*&&blksize/1024/1024) -      ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0

请注意:以上脚本针对如下情况,不能回收全部的空间:
比如一个10个GB的datafile,已经使用第2GB 和第9GB,从表空间使用率的角度看,Free空间是8GB, 用以上脚本仅仅能回收的空间肯定是远远小于8GB.

因为,resize的过程只是缩小Datafile的大小,resize并不负责move数据库对象(table 或者index).
Move的过程是把大号object_id上的row 搬家搬到小号object_id上的过程.