计算可以释放的空间有多少？

set lines 199 pagesize 100
set COLSEP '|'
col name for a60
select a.file#,
       a.name,
       round(a.bytes/1024/1024,2) CurrentMB,
       round((HWM * a.block_size)/1024/1024,2) ResizeTo,
       round((a.bytes - HWM * a.block_size)/1024/1024,2) ReleaseMB
  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) > 0
   and rownum < 10
   order by ReleaseMB;
复制

批量回收数据文件空间语句

set lines 199 pagesize 100
set COLSEP '|'
col RESIZECMD for a80
select a.file#,
       round(a.bytes/1024/1024,2) CurrentMB,
       round((HWM * a.block_size)/1024/1024,2) ResizeTo,
       round((a.bytes - HWM * a.block_size)/1024/1024,2) 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) > 0
   and rownum < 10
   order by ReleaseMB;
复制

set lines 199 pagesize 100
set COLSEP '|'
col RESIZECMD for a80
select a.file#,
       round(a.bytes/1024/1024,2) CurrentMB,
       round((HWM * a.block_size)/1024/1024,2) ResizeTo,
       round((a.bytes - HWM * a.block_size)/1024/1024,2) 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) > 0
   and rownum < 10
   order by ReleaseMB;



复制