Oracle-resize收缩数据文件脚本

Oracle resize收缩数据文件脚本

SELECT d.tablespace_name,
       a.file#,
       a.name,
       ceil(hwm * a.block_size) / 1024 / 1024 resizeto, --可以resize的目标大小
       a.bytes / 1024 / 1024 currentmb, --当前大小
       (a.bytes / 1024 / 1024) - (ceil(hwm * a.block_size) / 1024 / 1024) saveing_size_mb, --resize可以节省出的空间
       'alter database datafile ''' || a.name || ''' resize ' ||
       ceil(ceil(hwm * a.block_size) / 1024 / 1024) || 'M;' resize_script --resize脚本,需要ceil取整数
  FROM v$datafile a,
       (SELECT file_id,
               MAX(block_id + blocks - 1) hwm
          FROM dba_extents de
        ---    WHERE de.tablespace_name = 'PDMS_MIG_DATA'
         GROUP BY file_id) b,
       dba_data_files d
 WHERE a.file# = b.file_id
   AND d.file_id = a.file#
      --   AND d.tablespace_name = 'PDMS_MIG_DATA'
   AND (a.bytes / 1024 / 1024) - (ceil(hwm * a.block_size) / 1024 / 1024) > 128  --释放空间大于128MB
 ORDER BY 1,
          2;

上述方法计算不准确时可采用下列sql

--https://blog.dbi-services.com/resize-your-oracle-datafiles-down-to-the-minimum-without-ora-03297/

WITH hwm AS
 (
  -- get highest block id from each datafiles ( from x$ktfbue as we don't need all joins from dba_extents )
  SELECT /*+ materialize */
   ktfbuesegtsn ts#,
    ktfbuefno relative_fno,
    MAX(ktfbuebno + ktfbueblks - 1) hwm_blocks
    FROM sys.x$ktfbue
   GROUP BY ktfbuefno,
             ktfbuesegtsn),
hwmts AS
 (
  -- join ts# with tablespace_name
  SELECT NAME tablespace_name,
          relative_fno,
          hwm_blocks
    FROM hwm
    JOIN v$tablespace
   USING (ts#)),
hwmdf AS
 (
  -- join with datafiles, put 5M minimum for datafiles with no extents
  SELECT tablespace_name,
          file_name,
          nvl(hwm_blocks * (bytes / blocks),
              5 * 1024 * 1024) hwm_bytes,
          bytes,
          autoextensible,
          maxbytes
    FROM hwmts
   RIGHT JOIN dba_data_files
   USING (tablespace_name, relative_fno)
  -- where tablespace_name='APPS_DATA'
   )
SELECT tablespace_name,
       CASE
         WHEN autoextensible = 'YES'
              AND maxbytes >= bytes THEN -- we generate resize statements only if autoextensible can grow back to current size
          '/* reclaim ' || to_char(ceil((bytes - hwm_bytes) / 1024 / 1024),
                                   999999) || 'M from ' ||
          to_char(ceil(bytes / 1024 / 1024),
                  999999) || 'M */ ' || 'alter database datafile ''' ||
          file_name || ''' resize ' || ceil(hwm_bytes / 1024 / 1024) || 'M;'
         ELSE -- generate only a comment when autoextensible is off
          '/* reclaim ' || to_char(ceil((bytes - hwm_bytes) / 1024 / 1024),
                                   999999) || 'M from ' ||
          to_char(ceil(bytes / 1024 / 1024),
                  999999) ||
          'M after setting autoextensible maxsize higher than current size for file ' ||
          file_name || ' */'
       END SQL
  FROM hwmdf
 where 1=1 
 and tablespace_name not like '%UNDO%'
 and  bytes - hwm_bytes > 1024 * 1024 * 128 -- resize only if at least 128MB can be reclaimed
 ORDER BY bytes - hwm_bytes DESC