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