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