Reducir el tamaÃ±o de SYSAUX

David Lozano. domingo, 17 de noviembre de 2013.

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

Reducir el tamaÃ±o de SYSAUX

Purgado de estadÃ­sticas AWR

Shrink por etapas en SQL Server

Directorio de blogs en la nueva versiÃ³n de THOB.

Purgado de estadÃsticas AWR