语句拼接
select 'alter database datafile ''' || a.file_name || ''' resize ' ||
round(a.filesize - (a.filesize - c.hwmsize) * 0.8) || 'M;',
a.filesize || 'M' as "TOTAL",
c.hwmsize || 'M' as "USED"
from (select file_id, file_name, round(bytes / 1024 / 1024) as filesize
from dba_data_files) a,
(select file_id, round(max(block_id) * 8 / 1024 / 1024) as hwmsize
from dba_extents
group by file_id) c
where a.file_id = c.file_id
and a.filesize - c.hwmsize > 5000