Una de las tareas más difíciles en Oracle siempre ha sido reducir el tamaño de un datafile. No es muy habitual tener que hacerlo, pero tiene mucho sentido cuando eliminas una gran cantidad de datos definitivamente, por ejemplo, al borrar años de datos históricos.

El problema es que una vez eliminados los datos, queremos reducir los datafiles (Ej.:ALTER DATABASE DATAFILE '' RESIZE 200M;) del tablespace afectado y nos encontramos con el error ORA-03297 "file contains used data beyond requested value".

Esto es porque hay algún objeto que está físicamente al final del datafile, y nos impide reducirlo. La solución "clásica" era hacer un export/import del esquema, y ya podíamos reducir. Esta opción es un poco arriesgada, ya que hay que hacerla sin usuarios conectados, y puede llevarnos mucho tiempo.

A partir de Oracle 10 se simplificó un poco la cosa, ya que desde la consola de Enterprise Manager o Grid Control existe la acción de reorganizar el tablespace, y es un proceso totalmente automático.

Sin embargo, esta opción no está permitida para los tablespaces de sistema, por lo que vamos a ver cómo se reduciría el tablespace SYSAUX online y con el mínimo número de pasos.

El método consiste en identificar los objetos que están al final del datafile y mover solo éstos al principio. La tarea que podría llevarnos horas se reduce a minutos:
-- Crear un tablespace para mover temporalmente los ficheros, con suficiente espacio:

CREATE TABLESPACE sysaux_temp DATAFILE '/oradata002/AIPPRDDB/sysaux_temp.dbf' SIZE 3500M AUTOEXTEND ON NEXT 100M; 
-- Encontrar objetos que no nos permiten reducir el datafile, y moverlos al nuevo

SELECT *
FROM
  (SELECT OWNER,
          segment_name,
          segment_type,
          block_id
   FROM dba_extents
   WHERE file_id =
       (SELECT file_id
        FROM dba_data_files
        WHERE file_name = &FILE)
   ORDER BY block_id DESC)
WHERE rownum <= 50;

 Valor en este caso: '/oradata004/AIPPRDDB/sysaux04.dbf' 
-- Para conocer mover todas las particiones de una tabla a otro tablespace:

SELECT 'ALTER TABLE SYS.' || TABLE_NAME || ' MOVE PARTITION ' || PARTITION_NAME || ' TABLESPACE sysaux_tepm NOLOGGING;'
FROM DBA_TAB_PARTITIONS
WHERE TABLE_NAME IN ('WRH$_ACTIVE_SESSION_HISTORY',
                     'WRH$_EVENT_HISTOGRAM',
                     'WRH$_MVPARAMETER') 
--Las que hay que mover
-- Resultado:

ALTER TABLE SYS.WRH$_ACTIVE_SESSION_HISTORY MOVE PARTITION WRH$_ACTIVE_2308921431_20888 TABLESPACE sysaux_tepm NOLOGGING;
ALTER TABLE SYS.WRH$_ACTIVE_SESSION_HISTORY MOVE PARTITION WRH$_ACTIVE_2308921431_21080 TABLESPACE sysaux_tepm NOLOGGING;
ALTER TABLE SYS.WRH$_ACTIVE_SESSION_HISTORY MOVE PARTITION WRH$_ACTIVE_2308921431_21104 TABLESPACE sysaux_tepm NOLOGGING;
ALTER TABLE SYS.WRH$_ACTIVE_SESSION_HISTORY MOVE PARTITION WRH$_ACTIVE_2308921431_21176 TABLESPACE sysaux_tepm NOLOGGING;
ALTER TABLE SYS.WRH$_ACTIVE_SESSION_HISTORY MOVE PARTITION WRH$_ACTIVE_2308921431_21200 TABLESPACE sysaux_tepm NOLOGGING;
ALTER TABLE SYS.WRH$_ACTIVE_SESSION_HISTORY MOVE PARTITION WRH$_ACTIVE_2308921431_21224 TABLESPACE sysaux_tepm NOLOGGING;
ALTER TABLE SYS.WRH$_ACTIVE_SESSION_HISTORY MOVE PARTITION WRH$_ACTIVE_SES_MXDB_MXSN TABLESPACE sysaux_tepm NOLOGGING;
ALTER TABLE SYS.WRH$_EVENT_HISTOGRAM MOVE PARTITION WRH$_EVENT_HISTO_MXDB_MXSN TABLESPACE sysaux_tepm NOLOGGING;
ALTER TABLE SYS.WRH$_EVENT_HISTOGRAM MOVE PARTITION WRH$_EVENT__2308921431_20792 TABLESPACE sysaux_tepm NOLOGGING;
ALTER TABLE SYS.WRH$_EVENT_HISTOGRAM MOVE PARTITION WRH$_EVENT__2308921431_21104 TABLESPACE sysaux_tepm NOLOGGING;
ALTER TABLE SYS.WRH$_EVENT_HISTOGRAM MOVE PARTITION WRH$_EVENT__2308921431_21176 TABLESPACE sysaux_tepm NOLOGGING;
ALTER TABLE SYS.WRH$_EVENT_HISTOGRAM MOVE PARTITION WRH$_EVENT__2308921431_21200 TABLESPACE sysaux_tepm NOLOGGING;
ALTER TABLE SYS.WRH$_EVENT_HISTOGRAM MOVE PARTITION WRH$_EVENT__2308921431_21224 TABLESPACE sysaux_tepm NOLOGGING;
ALTER TABLE SYS.WRH$_MVPARAMETER MOVE PARTITION WRH$_MVPARAMETER_MXDB_MXSN TABLESPACE sysaux_tepm NOLOGGING;
ALTER TABLE SYS.WRH$_MVPARAMETER MOVE PARTITION WRH$_MVPARA_2308921431_20792 TABLESPACE sysaux_tepm NOLOGGING;
ALTER TABLE SYS.WRH$_MVPARAMETER MOVE PARTITION WRH$_MVPARA_2308921431_21104 TABLESPACE sysaux_tepm NOLOGGING;
ALTER TABLE SYS.WRH$_MVPARAMETER MOVE PARTITION WRH$_MVPARA_2308921431_21176 TABLESPACE sysaux_tepm NOLOGGING;
ALTER TABLE SYS.WRH$_MVPARAMETER MOVE PARTITION WRH$_MVPARA_2308921431_21200 TABLESPACE sysaux_tepm NOLOGGING;
ALTER TABLE SYS.WRH$_MVPARAMETER MOVE PARTITION WRH$_MVPARA_2308921431_21224 TABLESPACE sysaux_tepm NOLOGGING; -- Mover tablas
ALTER TABLE SYS.WRI$_SEGADV_OBJLIST MOVE TABLESPACE sysaux_tepm;
ALTER TABLE SYS.SCHEDULER$_EVENT_LOG MOVE TABLESPACE sysaux_tepm;
ALTER TABLE SYS.WRM$_SNAPSHOT_DETAILS MOVE TABLESPACE sysaux_tepm;
ALTER TABLE SYS.WRI$_ADV_ACTIONS MOVE TABLESPACE sysaux_tepm;
ALTER TABLE SYS.WRI$_ADV_OBJECTS MOVE TABLESPACE sysaux_tepm;
ALTER TABLE SYS.WRH$_SEG_STAT_OBJ MOVE TABLESPACE sysaux_tepm;
ALTER TABLE SYS.WRI$_ADV_MESSAGE_GROUPS MOVE TABLESPACE sysaux_tepm;
ALTER TABLE SYS.WRI$_ADV_RATIONALE MOVE TABLESPACE sysaux_tepm;
ALTER TABLE SYS.WRH$_RSRC_CONSUMER_GROUP MOVE TABLESPACE sysaux_tepm;
ALTER TABLE SYS.WRI$_ADV_MESSAGE_GROUPS MOVE TABLESPACE sysaux_tepm;
-- Rebuild índices

ALTER INDEX SYS.WRM$_SNAPSHOT_DETAILS_INDEX REBUILD TABLESPACE sysaux_tepm;
ALTER INDEX SYS.WRI$_ADV_MESSAGE_GROUPS_PK REBUILD TABLESPACE sysaux_tepm;
ALTER INDEX SYS.WRI$_ADV_PARAMETERS_PK REBUILD TABLESPACE sysaux_tepm;
ALTER INDEX SYS.WRI$_ADV_MSG_GRPS_IDX_01 REBUILD TABLESPACE sysaux_tepm;
-- Para conocer todas las particiones de índices que hay que mover:

SELECT 'ALTER INDEX SYS.' || INDEX_NAME || ' REBUILD PARTITION ' || PARTITION_NAME || ' TABLESPACE sysaux_tepm;'
FROM DBA_IND_PARTITIONS
WHERE INDEX_NAME IN ('WRH$_EVENT_HISTOGRAM_PK',
                     'WRH$_LATCH_PK'); 
-- Resultado:

ALTER INDEX SYS.WRH$_EVENT_HISTOGRAM_PK REBUILD PARTITION WRH$_EVENT_HISTO_MXDB_MXSN TABLESPACE sysaux_tepm;
ALTER INDEX SYS.WRH$_EVENT_HISTOGRAM_PK REBUILD PARTITION WRH$_EVENT__2308921431_20792 TABLESPACE sysaux_tepm;
ALTER INDEX SYS.WRH$_EVENT_HISTOGRAM_PK REBUILD PARTITION WRH$_EVENT__2308921431_21104 TABLESPACE sysaux_tepm;
ALTER INDEX SYS.WRH$_EVENT_HISTOGRAM_PK REBUILD PARTITION WRH$_EVENT__2308921431_21176 TABLESPACE sysaux_tepm;
ALTER INDEX SYS.WRH$_EVENT_HISTOGRAM_PK REBUILD PARTITION WRH$_EVENT__2308921431_21200 TABLESPACE sysaux_tepm;
ALTER INDEX SYS.WRH$_EVENT_HISTOGRAM_PK REBUILD PARTITION WRH$_EVENT__2308921431_21224 TABLESPACE sysaux_tepm;
ALTER INDEX SYS.WRH$_LATCH_PK REBUILD PARTITION WRH$_LATCH_2308921431_20960 TABLESPACE sysaux_tepm;
ALTER INDEX SYS.WRH$_LATCH_PK REBUILD PARTITION WRH$_LATCH_2308921431_21032 TABLESPACE sysaux_tepm;
ALTER INDEX SYS.WRH$_LATCH_PK REBUILD PARTITION WRH$_LATCH_2308921431_21080 TABLESPACE sysaux_tepm;
ALTER INDEX SYS.WRH$_LATCH_PK REBUILD PARTITION WRH$_LATCH_2308921431_21104 TABLESPACE sysaux_tepm;
ALTER INDEX SYS.WRH$_LATCH_PK REBUILD PARTITION WRH$_LATCH_2308921431_21176 TABLESPACE sysaux_tepm;
ALTER INDEX SYS.WRH$_LATCH_PK REBUILD PARTITION WRH$_LATCH_2308921431_21200 TABLESPACE sysaux_tepm;
ALTER INDEX SYS.WRH$_LATCH_PK REBUILD PARTITION WRH$_LATCH_2308921431_21224 TABLESPACE sysaux_tepm;
ALTER INDEX SYS.WRH$_LATCH_PK REBUILD PARTITION WRH$_LATCH_MXDB_MXSN TABLESPACE sysaux_tepm; 
-- Ver tamaño del bloque

SELECT value
FROM v$parameter
WHERE name = 'db_block_size';
-- Para ver el máximo que podemos reducir (Bloque 8192, tablespace SYSAUX)

SELECT bytes/1024/1024 real_size,
       ceil((nvl(hwm,1)*&DB_BLOCK_SIZE)/1024/1024) shrinked_size,
       bytes/1024/1024-ceil((nvl(hwm,1)*&DB_BLOCK_SIZE)/1024/1024) released_size ,
                                                                   'alter database datafile '|| ''''||file_name||'''' || ' resize ' || ceil((nvl(hwm,1)*&DB_BLOCK_SIZE)/1024/1024) || ' m;' cmd
FROM dba_data_files a,
  (SELECT file_id,
          max(block_id+blocks-1) hwm
   FROM dba_extents
   GROUP BY file_id) b
WHERE tablespace_name='&ts_name'
  AND a.file_id = b.file_id(+)
  AND ceil(blocks*&DB_BLOCK_SIZE/1024/1024)- ceil((nvl(hwm,1)* &DB_BLOCK_SIZE)/1024/1024) > 0;
--Volver a poner todo en SYSAUX

ALTER TABLE SYS.WRH$_ACTIVE_SESSION_HISTORY MOVE PARTITION WRH$_ACTIVE_2308921431_20888 TABLESPACE SYSAUX NOLOGGING;
ALTER TABLE SYS.WRH$_ACTIVE_SESSION_HISTORY MOVE PARTITION WRH$_ACTIVE_2308921431_21080 TABLESPACE SYSAUX NOLOGGING;
ALTER TABLE SYS.WRH$_ACTIVE_SESSION_HISTORY MOVE PARTITION WRH$_ACTIVE_2308921431_21104 TABLESPACE SYSAUX NOLOGGING;
ALTER TABLE SYS.WRH$_ACTIVE_SESSION_HISTORY MOVE PARTITION WRH$_ACTIVE_2308921431_21176 TABLESPACE SYSAUX NOLOGGING;
ALTER TABLE SYS.WRH$_ACTIVE_SESSION_HISTORY MOVE PARTITION WRH$_ACTIVE_2308921431_21200 TABLESPACE SYSAUX NOLOGGING;
ALTER TABLE SYS.WRH$_ACTIVE_SESSION_HISTORY MOVE PARTITION WRH$_ACTIVE_2308921431_21224 TABLESPACE SYSAUX NOLOGGING;
ALTER TABLE SYS.WRH$_ACTIVE_SESSION_HISTORY MOVE PARTITION WRH$_ACTIVE_SES_MXDB_MXSN TABLESPACE SYSAUX NOLOGGING;
ALTER TABLE SYS.WRH$_EVENT_HISTOGRAM MOVE PARTITION WRH$_EVENT_HISTO_MXDB_MXSN TABLESPACE SYSAUX NOLOGGING;
ALTER TABLE SYS.WRH$_EVENT_HISTOGRAM MOVE PARTITION WRH$_EVENT__2308921431_20792 TABLESPACE SYSAUX NOLOGGING;
ALTER TABLE SYS.WRH$_EVENT_HISTOGRAM MOVE PARTITION WRH$_EVENT__2308921431_21104 TABLESPACE SYSAUX NOLOGGING;
ALTER TABLE SYS.WRH$_EVENT_HISTOGRAM MOVE PARTITION WRH$_EVENT__2308921431_21176 TABLESPACE SYSAUX NOLOGGING;
ALTER TABLE SYS.WRH$_EVENT_HISTOGRAM MOVE PARTITION WRH$_EVENT__2308921431_21200 TABLESPACE SYSAUX NOLOGGING;
ALTER TABLE SYS.WRH$_EVENT_HISTOGRAM MOVE PARTITION WRH$_EVENT__2308921431_21224 TABLESPACE SYSAUX NOLOGGING;
ALTER TABLE SYS.WRH$_MVPARAMETER MOVE PARTITION WRH$_MVPARAMETER_MXDB_MXSN TABLESPACE SYSAUX NOLOGGING;
ALTER TABLE SYS.WRH$_MVPARAMETER MOVE PARTITION WRH$_MVPARA_2308921431_20792 TABLESPACE SYSAUX NOLOGGING;
ALTER TABLE SYS.WRH$_MVPARAMETER MOVE PARTITION WRH$_MVPARA_2308921431_21104 TABLESPACE SYSAUX NOLOGGING;
ALTER TABLE SYS.WRH$_MVPARAMETER MOVE PARTITION WRH$_MVPARA_2308921431_21176 TABLESPACE SYSAUX NOLOGGING;
ALTER TABLE SYS.WRH$_MVPARAMETER MOVE PARTITION WRH$_MVPARA_2308921431_21200 TABLESPACE SYSAUX NOLOGGING;
ALTER TABLE SYS.WRH$_MVPARAMETER MOVE PARTITION WRH$_MVPARA_2308921431_21224 TABLESPACE SYSAUX NOLOGGING;
ALTER TABLE SYS.WRI$_SEGADV_OBJLIST MOVE TABLESPACE SYSAUX;
ALTER TABLE SYS.SCHEDULER$_EVENT_LOG MOVE TABLESPACE SYSAUX;
ALTER TABLE SYS.WRM$_SNAPSHOT_DETAILS MOVE TABLESPACE SYSAUX;
ALTER TABLE SYS.WRI$_ADV_ACTIONS MOVE TABLESPACE SYSAUX;
ALTER TABLE SYS.WRI$_ADV_OBJECTS MOVE TABLESPACE SYSAUX;
ALTER TABLE SYS.WRH$_SEG_STAT_OBJ MOVE TABLESPACE SYSAUX;
ALTER TABLE SYS.WRI$_ADV_MESSAGE_GROUPS MOVE TABLESPACE SYSAUX;
ALTER TABLE SYS.WRI$_ADV_RATIONALE MOVE TABLESPACE SYSAUX;
ALTER TABLE SYS.WRH$_RSRC_CONSUMER_GROUP MOVE TABLESPACE SYSAUX;
ALTER TABLE SYS.WRI$_ADV_MESSAGE_GROUPS MOVE TABLESPACE SYSAUX;
ALTER INDEX SYS.WRM$_SNAPSHOT_DETAILS_INDEX REBUILD TABLESPACE SYSAUX;
ALTER INDEX SYS.WRI$_ADV_MESSAGE_GROUPS_PK REBUILD TABLESPACE SYSAUX;
ALTER INDEX SYS.WRI$_ADV_PARAMETERS_PK REBUILD TABLESPACE SYSAUX;
ALTER INDEX SYS.WRI$_ADV_MSG_GRPS_IDX_01 REBUILD TABLESPACE SYSAUX;
ALTER INDEX SYS.WRH$_EVENT_HISTOGRAM_PK REBUILD PARTITION WRH$_EVENT_HISTO_MXDB_MXSN TABLESPACE SYSAUX;
ALTER INDEX SYS.WRH$_EVENT_HISTOGRAM_PK REBUILD PARTITION WRH$_EVENT__2308921431_20792 TABLESPACE SYSAUX;
ALTER INDEX SYS.WRH$_EVENT_HISTOGRAM_PK REBUILD PARTITION WRH$_EVENT__2308921431_21104 TABLESPACE SYSAUX;
ALTER INDEX SYS.WRH$_EVENT_HISTOGRAM_PK REBUILD PARTITION WRH$_EVENT__2308921431_21176 TABLESPACE SYSAUX;
ALTER INDEX SYS.WRH$_EVENT_HISTOGRAM_PK REBUILD PARTITION WRH$_EVENT__2308921431_21200 TABLESPACE SYSAUX;
ALTER INDEX SYS.WRH$_EVENT_HISTOGRAM_PK REBUILD PARTITION WRH$_EVENT__2308921431_21224 TABLESPACE SYSAUX;
ALTER INDEX SYS.WRH$_LATCH_PK REBUILD PARTITION WRH$_LATCH_2308921431_20960 TABLESPACE SYSAUX;
ALTER INDEX SYS.WRH$_LATCH_PK REBUILD PARTITION WRH$_LATCH_2308921431_21032 TABLESPACE SYSAUX;
ALTER INDEX SYS.WRH$_LATCH_PK REBUILD PARTITION WRH$_LATCH_2308921431_21080 TABLESPACE SYSAUX;
ALTER INDEX SYS.WRH$_LATCH_PK REBUILD PARTITION WRH$_LATCH_2308921431_21104 TABLESPACE SYSAUX;
ALTER INDEX SYS.WRH$_LATCH_PK REBUILD PARTITION WRH$_LATCH_2308921431_21176 TABLESPACE SYSAUX;
ALTER INDEX SYS.WRH$_LATCH_PK REBUILD PARTITION WRH$_LATCH_2308921431_21200 TABLESPACE SYSAUX;
ALTER INDEX SYS.WRH$_LATCH_PK REBUILD PARTITION WRH$_LATCH_2308921431_21224 TABLESPACE SYSAUX;
ALTER INDEX SYS.WRH$_LATCH_PK REBUILD PARTITION WRH$_LATCH_MXDB_MXSN TABLESPACE SYSAUX;
-- Borrar el tablespace temporal

DROP TABLESPACE sysaux_temp;

rm /oradata002/AIPPRDDB/sysaux_temp.dbf