Oracle Query - Reclaim / Shrink Datafile

Olá, neste artigo deixo uma query para que vocês possam realizar o shrink/reclaim do espaço disponível nos datafiles.

As vezes realizamos o resize dos datafiles alocando mais espaço do que o necessário ou é realizada alguma rotina de expurgo dos dados em alguma tabela e necessitamos recuperar o espaço fisicamente no disco.

A query abaixo trará o comando necessário a ser executado para realizar o downsize do datafile até o limite máximo permitido dos blocos no header (High water mark).

Query

SET LINES 200 pages 2000
SET long 9999999
 
SELECT tablespace_name,
       'alter database datafile ''' || file_name || ''' resize ' || CEIL((nvl(hwm,1) * 8192)/1024/1024 + 5) || 'M;' AS cmd,
       CEIL( (nvl(hwm,1) * 8192)/1024/1024 ) smallest,
       CEIL( blocks * 8192/1024/1024) currsize,
       CEIL( blocks * 8192/1024/1024) - CEIL((nvl(hwm,1) * 8192)/1024/1024) savings
FROM dba_data_files a,
     ( SELECT file_id, MAX(block_id+blocks-1) hwm
         FROM dba_extents
        GROUP BY file_id ) b
WHERE a.file_id = b.file_id(+) 
AND CEIL( blocks * 8192/1024/1024) - CEIL((nvl(hwm,1) * 8192)/1024/1024) > 0
ORDER BY savings DESC
/

Abaixo uma outra variação de execução apenas numa tablespace específica:

SET LINES 200 pages 2000
SET long 9999999
DEFINE tbsp = '&TABLESPACE_NAME';
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#)
  WHERE NAME = '&tbsp'
 ),
 HWMDF AS (
  -- JOIN WITH DATAFILES, PUT 50M MINIMUM FOR DATAFILES WITH NO EXTENTS
  SELECT TABLESPACE_NAME,FILE_NAME,NVL(HWM_BLOCKS*(BYTES/BLOCKS),50*1024*1024) HWM_BYTES,BYTES,AUTOEXTENSIBLE,MAXBYTES
  FROM HWMTS RIGHT JOIN DBA_DATA_FILES USING(TABLESPACE_NAME,RELATIVE_FNO)
 )
SELECT
 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
TABLESPACE_NAME = '&tbsp' 
AND BYTES-HWM_BYTES>1024*1024 -- RESIZE ONLY IF AT LEAST 1MB CAN BE RECLAIMED
ORDER BY BYTES-HWM_BYTES DESC
/

Abaixo uma outra variação para todas as tablespaces:

SET LINES 200 pages 2000
SET long 9999999
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 50M MINIMUM FOR DATAFILES WITH NO EXTENTS
  SELECT FILE_NAME,NVL(HWM_BLOCKS*(BYTES/BLOCKS),50*1024*1024) HWM_BYTES,BYTES,AUTOEXTENSIBLE,MAXBYTES
  FROM HWMTS RIGHT JOIN DBA_DATA_FILES USING(TABLESPACE_NAME,RELATIVE_FNO)
 )
SELECT
 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
 BYTES-HWM_BYTES>1024*1024 -- RESIZE ONLY IF AT LEAST 1MB CAN BE RECLAIMED
ORDER BY BYTES-HWM_BYTES DESC
/

- Até a próxima.

Oracle Query - Reclaim / Shrink Datafile

— Autor: Leonardo Lopes 08/07/2025 08H:57