1、查看可以收缩的数据文件,
select * from dba_free_space where file_id in (
select file_id from dba_free_space group by file_id having count(0)=1 );
select * from dba_data_files where file_id in (
select file_id from dba_free_space group by file_id having count(0)=1 );
select a.file#,
a.name,
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.name || ''' resize ' ||
ceil(HWM * a.block_size / 1024 / 1024) || 'M;' ResizeCmd
from v$datafile a,
(SELECT file_id, MAX(block_id + blocks - 1) HWM
FROM DBA_EXTENTS
GROUP BY file_id) b
where a.file# = b.file_id(+)
And (a.bytes - HWM * a.block_size) / 1024 / 1024 > 100
-- and rownum < 10