ALTER TABLE … SHRINK SPACE Command: Reducción de segmento en línea para tablas, LOB e IOT

Base de datos, DBA-Avanzadas, Oracle

El comando ALTER TABLE … SHRINK SPACE se introdujo en Oracle 10g para realizar reducciones de segmentos en línea para tablas, LOB y segmentos de desbordamiento de IOT.

  • Ejemplos de reducción de espacio
  • Identificar segmentos grandes
  • Movimiento de fila
  • LOBs de SecureFile
  • Comentarios y restricciones

Ejemplos de reducción de espacio

A continuación se muestran algunos ejemplos sencillos del comando ALTER TABLE … SHRINK SPACE.

-- Enable row movement.
ALTER TABLE scott.emp ENABLE ROW MOVEMENT;

-- Recover space and amend the high water mark (HWM).
ALTER TABLE scott.emp SHRINK SPACE;

-- Recover space, but don't amend the high water mark (HWM).
ALTER TABLE scott.emp SHRINK SPACE COMPACT;

-- Recover space for the object and all dependant objects.
ALTER TABLE scott.emp SHRINK SPACE CASCADE;

La opción COMPACT permite dividir la operación de contracción en dos etapas. Primero, las filas se mueven usando la opción COMPACT, pero la marca de agua alta (HWM) no se ajusta, por lo que no se invalidan las declaraciones SQL analizadas. El HWM se puede ajustar en una fecha posterior volviendo a emitir el estado de cuenta sin la opción COMPACT. En este punto, será necesario volver a analizar cualquier declaración SQL dependiente.

A continuación se muestran otros comandos de reducción de interés.

-- Shrink a LOB segment (basicfile only).
ALTER TABLE table_name MODIFY LOB(lob_column) (SHRINK SPACE);
ALTER TABLE table_name MODIFY LOB(lob_column) (SHRINK SPACE CASCADE);

-- Shrink an IOT overflow segment.
ALTER TABLE iot_name OVERFLOW SHRINK SPACE;

Hay más detalles sobre esta funcionalidad a continuación.

Identificar segmentos grandes

Las vistas DBA | ALL | USER_SEGMENTS se pueden utilizar para identificar segmentos grandes. El siguiente ejemplo usa una consulta top-N para mostrar los 20 segmentos más grandes.

SET LINESIZE 200
COLUMN owner FORMAT A30
COLUMN segment_name FORMAT A30
COLUMN tablespace_name FORMAT A30
COLUMN size_mb FORMAT 99999999.00

SELECT *
FROM   (SELECT owner,
               segment_name,
               segment_type,
               tablespace_name,
               ROUND(bytes/1024/1024,2) size_mb
        FROM   dba_segments
        ORDER BY 5 DESC)
WHERE  ROWNUM <= 20;

Puede ver que muchos de los segmentos más grandes son segmentos LOB. Puede obtener más información sobre los segmentos LOB específicamente utilizando la siguiente consulta top-N.

Los siguientes scripts son ejemplos de este tipo de consultas.

SET LINESIZE 200
COLUMN owner FORMAT A30
COLUMN table_name FORMAT A30
COLUMN column_name FORMAT A30
COLUMN segment_name FORMAT A30
COLUMN tablespace_name FORMAT A30
COLUMN size_mb FORMAT 99999999.00

SELECT *
FROM   (SELECT l.owner,
               l.table_name,
               l.column_name,
               l.segment_name,
               l.tablespace_name,
               ROUND(s.bytes/1024/1024,2) size_mb
        FROM   dba_lobs l
               JOIN dba_segments s ON s.owner = l.owner AND s.segment_name = l.segment_name
        ORDER BY 6 DESC)
WHERE  ROWNUM <= 20;

--Displays size of large LOB segments.
SET LINESIZE 500 VERIFY OFF
COLUMN owner FORMAT A30
COLUMN segment_name FORMAT A30
COLUMN tablespace_name FORMAT A30
COLUMN size_mb FORMAT 99999999.00

SELECT *
FROM   (SELECT owner,
               segment_name,
               segment_type,
               tablespace_name,
               ROUND(bytes/1024/1024,2) size_mb
        FROM   dba_segments
        ORDER BY 5 DESC)
WHERE  ROWNUM <= &1;

SET VERIFY ON

Movimiento de fila

El comando ALTER TABLE ... SHRINK SPACE mueve filas entre bloques existentes para compactar los datos, por lo que antes de intentar reducir un segmento de tabla, debe habilitar el movimiento de filas. Puede comprobar si el movimiento de filas ya está habilitado consultando la columna ROW_MOVEMENT de las vistas [DBA | ALL | USER] _TABLES.

SELECT row_movement
FROM   user_tables
WHERE  table_name = 'EMP';

ROW_MOVE
--------
DISABLED

SQL>

El movimiento de filas se habilita con el siguiente comando.

ALTER TABLE emp ENABLE ROW MOVEMENT;

La repetición de la consulta anterior muestra que el movimiento de filas ahora está habilitado.

SELECT row_movement
FROM   user_tables
WHERE  table_name = 'EMP';

ROW_MOVE
--------
ENABLED

SQL>

LOB de SecureFile

Cuando se utilizan LOB de archivos básicos, los comandos de reducción funcionan como se esperaba. Para demostrar esto, necesitamos crear la siguiente tabla que contiene una columna LOB de archivo básico.

DROP TABLE lob_tab PURGE;

CREATE TABLE lob_tab (
  id NUMBER,
  data CLOB
)
LOB(data) STORE AS BASICFILE (DISABLE STORAGE IN ROW);

INSERT INTO lob_tab VALUES (1, 'ONE');
COMMIT;

Podemos ver ambos comandos de reducción completa sin errores.

SQL> ALTER TABLE lob_tab MODIFY LOB(data) (SHRINK SPACE);

Table altered.

SQL> ALTER TABLE lob_tab MODIFY LOB(data) (SHRINK SPACE CASCADE);

Table altered.

SQL>

Ahora vuelva a crear la tabla utilizando una columna LOB de archivo seguro.

DROP TABLE lob_tab PURGE;

CREATE TABLE lob_tab (
  id NUMBER,
  data CLOB
)
LOB(data) STORE AS SECUREFILE (DISABLE STORAGE IN ROW);

INSERT INTO lob_tab VALUES (1, 'ONE');
COMMIT;

Ahora el primer comando falla, pero parece que agregar la opción CASCADE para que funcione.

SQL> ALTER TABLE lob_tab MODIFY LOB(data) (SHRINK SPACE);
ALTER TABLE lob_tab MODIFY LOB(data) (SHRINK SPACE)
*
ERROR at line 1:
ORA-10635: Invalid segment or tablespace type


SQL> ALTER TABLE lob_tab MODIFY LOB(data) (SHRINK SPACE CASCADE);

Table altered.

SQL>

Desafortunadamente, el segundo comando no funciona y el segmento LOB del archivo seguro no se reduce.

En cambio, para reducir un segmento de LOB de archivo seguro, debe moverlo. En el siguiente ejemplo, el movimiento es al mismo espacio de tabla.

ALTER TABLE lob_tab MOVE LOB(data) STORE AS (TABLESPACE users);

Comentarios y restricciones

A continuación, se incluyen algunos aspectos a considerar antes de realizar operaciones de encogimiento.

  •  Mover filas puede causar problemas con los disparadores basados en rowid.
  •  Las vistas materializadas de Rowid deben reconstruirse después de una operación de contracción.
  •  El proceso de reducción solo está disponible para objetos en espacios de tabla con la gestión automática del espacio de segmento habilitada.
  •  No puede combinar la cláusula SHRINK SPACE con ninguna otra cláusula ALTER TABLE.
  •  No se puede reducir un grupo o una tabla agrupada.
  •  No puede encoger ningún objeto con una columna LARGA.
  •  No puede reducir las tablas con índices dependientes basados en funciones, índices de dominio o índices de unión de mapa de bits.
  •  No puede reducir las tablas que son la tabla maestra de una vista materializada ON COMMIT.
  •  Las tablas de asignación de tablas organizadas por índices no se ven afectadas por una reducción.
  •  Los encogimientos no se pueden usar para tablas comprimidas, excepto aquellas que usan Compresión de filas avanzada (ROW STORE COMPRESS ADVANCED).
  •  La operación de encogimiento contra una mesa no se distribuye en cascada a los segmentos LOB. Deben manejarse por separado.
  •  No se pueden reducir los segmentos LOB de archivos seguros.
  •  Cambiar la disposición de las filas en una tabla puede tener un impacto negativo en el rendimiento en algunas circunstancias. Pruebe a fondo antes de tomar cualquier decisión.
  •  Después de cualquier cambio estructural, como un movimiento, recuerde buscar índices inutilizables. Puede utilizar el script unusuable_indexes.sql para encontrarlos. Si tiene alguno, reconstrúyalo.

Quizás también te puedas interesar:

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *