数据文件resize回收空间

问题描述：有一个数据文件缩容的需求，提前测试下，回收完要做表压缩的操作

 

1.查询可以resize的数据文件

select a.file#,
a.name,
c.tablespace_name,
round(a.bytes/1024/1024) CurrentMB,
ceil(HWM *a.block_size)/1024/1024 ResizeTo,
(a.bytes - HWM*a.block_size)/1024/1024 ReleaseMB,
'alter database datafile ' || a.FILE# || ' resize ' ||
round(ceil(HWM*a.block_size)/1024/1024+5)||'M;' ResizeCmd
 from v$datafile a,
(SELECT file_id,MAX(block_id+blocks-1) HWM
FROM DBA_EXTENTS
GROUP BY file_id) b,
dba_data_files c
 where a.file# = b.file_id(+)
And (a.bytes - HWM * a.block_size) >0
and a.FILE#=c.file_id
and c.tablespace_name not in ('SYSTEM','SYSAUX')
and c.tablespace_name not like '%UNDO%'
 order by 6 desc;

 

 

 

2.查询数据文件的大小

select file_name,bytes/1024/1024/1024 as GB from dba_data_files

 

 

 

 

3.进行数据文件resize的回收

alter database datafile 4 resize 1363M;
alter database datafile 8 resize 574M;
alter database datafile 7 resize 6M;
alter database datafile 9 resize 6M;
alter database datafile 6 resize 16M;
alter database datafile 5 resize 318M;

 

 

3.查看数据文件大小，可以看到数据文件已经变小

select file_name,bytes/1024/1024/1024 as GB from dba_data_files