Monthly Archives: 6 月 2015

压缩Oracle表空间

by 
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 * Block_Size) > 0