oracle 11g 压缩数据文件

通过以下语句直接分析出每个数据库文件可压缩量

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;'||

       'alter database datafile ''' || a.name || ''' autoextend on next 100M;'

        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 * block_size) > 0

 order by 5 desc