ORACLE 表空间回收

语句拼接

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