Oracle收缩表空间resize

Oracle收缩表空间resize

-- Oracle收缩表空间resize
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
 ORDER BY 5;

alter database datafile 'D:\ORACLE_DATABASE\ORADATA\TZQ\USERS01.DBF' resize 2M;
alter database datafile 'D:\ORACLE_DATABASE\ORADATA\TZQ\SYSTEM01.DBF' resize 744M;
alter database datafile 'D:\ORACLE_DATABASE\ORADATA\TZQ\SYSAUX01.DBF' resize 739M;
alter database datafile 'D:\ORACLE_DATABASE\ORADATA\TZQ\UNDOTBS01.DBF' resize 23M;
alter database datafile 'D:\ORACLE_DATABASE\ORADATA\TZQ\LOG.DBF' resize 17M;