Oracle 23ia ha introducido un nuevo procedimiento SHRINK_TABLESPACE dentro de DBMS_SPACE.

¿Qué hace este procedimiento? Hacernos más fácil la recuperación de espacio en los tablespaces cuando se ejecutan deletes y updates en las tablas que pertenecen a ese tablespace.

La gestión eficiente del espacio de los tablespaces es algo crucial para mantener un rendimiento óptimo en las bases de datos.

Esta operación se solía llevar a cabo en versiones anteriores de la siguiente manera:

Truncate table: Esta operación no siempre puede llevarse a cabo, ya que las tablas que están dentro de ese tablespace pueden contener datos sensibles y no siempre pueden limpiarse. Muy importante saber que este tipo de operación lleva implícito commit porque se trata de lenguaje DDL y no DML.
Enable Row Movement: Para poder realizar el Shrink en un tablespace, es necesario habilitar el movimiento de filas dentro del mismo. Esta operación es obligatoria para poder reclamar espacio.

La sentencia sería la siguiente:
```
  ALTER TABLE <OWNER>.<TABLA> ENABLE ROW MOVEMENT;
```
Os dejo aquí un pequeño ejemplo de un bloque anónimo para habilitarlo en todas las tablas que están dentro de un tablespace.

  Begin
    For r In (
          Select 'Alter Table '|| Owner ||'.'|| Table_Name || ' Enable Row Movement' sentencia
            From Dba_Tables
           Where Tablespace_Name = '&TABLESPACE'
             And Partitioned = 'NO'
              ) Loop
      Execute Immediate r.sentencia;
    End Loop;
   End;

Shrink Space / Shrink Space Compact / Shrink Space Cascade: Esta sería la última operación. La diferencia del primero con respecto a los otros dos es que el primero no siempre limpia al 100% los huecos libres, cosa que los otros dos sí.

Respecto al “Shrink Space Cascade“ y “Shrink Space Compact“ hacen lo mismo, excepto que el primero también lo hace a los objetos que dependen de él.

La sentencia sería la siguiente:

  ALTER TABLE <OWNER>.<TABLA> SHRINK SPACE;
  ALTER TABLE <OWNER>.<TABLA> SHRINK SPACE COMPACT;
  ALTER TABLE <OWNER>.<TABLA> SHRINK SPACE CASCADE;

Igual que el punto dos, dejo por aquí un bloque anónimo para ejecutarlo de manera dinámica a todas las tablas que estarían dentro del tablespace.

  Begin
    For r In (
          Select 'Alter Table '|| Owner ||'.'|| Table_Name || ' Shrink Space Cascade' sentencia
            From Dba_Tables
           Where Tablespace_Name = '&TABLESPACE'
             And Partitioned = 'NO'
              ) Loop
      Execute Immediate r.sentencia;
    End Loop;
  End;

Todos estos bloque anónimos se encuentran en github

dbaenlasombra/SHRINK_TABLESPACE

Vamos hacer este tipo de operación dentro de nuestra 23ai, este ejemplo constará de lo siguiente:

Definición de un nuevo tablespace dentro del schema HR.
Definición de tablas con diferente volumetría.
Uso de la nueva función SHRINK_TABLESPACE.

¡Manos a la obra! El primero paso que vamos hacer es definir un tablespace:

SQL> CREATE TABLESPACE TBS_SPECIAL DATAFILE 'TBS_SPECIAL.dbf' SIZE 10M AUTOEXTEND ON;

Tablespace created.

Una cosa importante de esta nueva versión, aparte de introducir muchas mejoras y funcionalidades, es que ahora en la nueva versión por defecto los tablespaces son de tipo BIGFILE.

SQL> Select tablespace_Name, bigfile From Dba_tablespaces;

TABLESPACE_NAME            BIG
------------------------------ ---
SYSTEM                   YES
SYSAUX                   YES
UNDOTBS1               YES
TEMP                   NO
USERS                   YES
TBS_SPECIAL               YES
6 rows selected.

Revisamos el tamaño del tablespace que hemos creado:

SQL> r
  1  Select a.Tablespace_Name, a.Bigfile, b.Blocks
  2    From Dba_Tablespaces a, Dba_Data_Files b
  3   Where b.Tablespace_Name = a.Tablespace_Name
  4*    And a.Tablespace_Name = 'TBS_SPECIAL'

TABLESPACE_NAME        BIG     BLOCKS
---------------------- --- ----------
TBS_SPECIAL            YES     1280

Y vemos que el tamaño es de 1280 bloques. Vamos a crear dos tablas de distinto tamaño:

SQL> r
  1* Create table t_Objects tablespace TBS_Special As 
      Select o.* From Dba_Segments o Cross Join Dba_Views p
Table created.
SQL> r
  1* Create table t_Segmentos tablespace TBS_Special As 
      Select o.* From Dba_Segments o Cross Join Dba_Segments r
Table created.

Si volvemos a revisar el tamaño del tablespaces, veremos que ha incrementado considerablemente, ha pasado de 1280 bloques a tener 984576.

A nivel de unix también podemos ver el tamaño del datafile:

SQL>  ! ls -laSh /opt/oracle/product/23ai/dbhomeFree/dbs/TBS_SPECIAL.dbf
-rw-r-----. 1 oracle oinstall 7.6G Jan 10 08:28 /opt/oracle/product/23ai/dbhomeFree/dbs/TBS_SPECIAL.dbf

Vamos a realizar el truncado de la tabla T_OBJECTS y revisamos el tamaño de nuevo a ver que sucede.

SQL> truncate table T_OBJECTS;

Table truncated.

SQL> ! ls -laSh /opt/oracle/product/23ai/dbhomeFree/dbs/TBS_SPECIAL.dbf
-rw-r-----. 1 oracle oinstall 7.6G Jan 10 08:28 /opt/oracle/product/23ai/dbhomeFree/dbs/TBS_SPECIAL.dbf

SQL> r
  1  Select a.Tablespace_Name,
  2        a.Bigfile,
  3        b.Blocks,
  4        Round(b.Bytes / 1024 / 1024) As Mbytes
  5    From Dba_Tablespaces a,
  6        Dba_Data_Files b
  7   Where b.Tablespace_Name = a.Tablespace_Name
  8*    And a.Tablespace_Name = 'TBS_SPECIAL'

TABLESPACE_NAME            BIG     BLOCKS      MBYTES
------------------------------ --- ---------- ----------
TBS_SPECIAL               YES     984576        7692

Aún habiendo hecho un truncate de la table, el espacio sobrante no ha sido corregido y el tamaño del datafile no ha variado.

Vamos a utilizar la nueva función que nos brinda Oracle para limpiar los bloques vacíos. Para ello vamos hacer dos pasos, uno de estimación y otro de confirmación.

Primero vamos a estimar el espacio que podemos recuperar. Para poder estimar el espacio, debemos usar el parámetro shrink_mode con valor DBMS_SPACE.TS_MODE_ANALYZE. Importante este paso, porque de no incluirlo, nos realizaría directamente la limpieza de huecos vacíos.

SQL> Exec dbms_space.shrink_tablespace(ts_name => 'TBS_SPECIAL',shrink_mode => DBMS_SPACE.TS_MODE_ANALYZE);
-------------------ANALYZE RESULT-------------------
Total Movable Objects: 1
Total Movable Size(GB): 2.19
Original Datafile Size(GB): 7.51
Suggested Target Size(GB): 7.51
Process Time: +00 00:00:11.818797

PL/SQL procedure successfully completed.

En este primer paso, podemos ver que quedaría un único objeto en el tablespace (Total Movable Objects: 1) de un tamaño de 2Gb (Total Movable Size(GB): 2.19), también nos daría información del tamaño del datafile (Original Datafile Size(GB)) y como quedaría (Suggested Target Size(GB): 7.51).

Suena raro que Original y Suggested tenga el mismo valor cuando no debería ser así ya que hemos limpiado la tabla con mayor volumen.

Vamos a confirmar la operación y vamos a ejecutar el mismo procedimiento que en el paso previo, pero en vez TS_MODE_ANALYZE, lo vamos a ejecutar TS_MODE_SHRINK. También el tiempo de ejecución varia entre ambos como podemos ver en “Process Time”.

SQL> Exec dbms_space.shrink_tablespace(ts_name => 'TBS_SPECIAL',shrink_mode => DBMS_SPACE.ts_mode_shrink);
-------------------SHRINK RESULT-------------------
Total Moved Objects: 1
Total Moved Size(GB): 2.19
Original Datafile Size(GB): 7.51
New Datafile Size(GB): 2.65
Process Time: +00 00:03:20.620325

PL/SQL procedure successfully completed.

En este último paso podemos ver que con este limpieza de bloques, el tamaño que tendría ahora nuestro datafile sería de 2,65Gb (New Datafile Size(GB): 2.65). Podemos ver que el proceso movió 2,19Gb desde el final del datafile al comienzo con una reducción del mismo de 7,51Gb a 2,65Gb.

Revisamos si el tamaño del tablespaces concuerda con la operación que acabamos de realizar:

SQL> r
  1  Select a.Tablespace_Name,
  2        a.Bigfile,
  3        b.Blocks,
  4        Round(b.Bytes / 1024 / 1024) As Mbytes
  5    From Dba_Tablespaces a,
  6        Dba_Data_Files b
  7   Where b.Tablespace_Name = a.Tablespace_Name
  8*    And a.Tablespace_Name = 'TBS_SPECIAL'

TABLESPACE_NAME            BIG     BLOCKS      MBYTES
------------------------------ --- ---------- ----------
TBS_SPECIAL               YES     347136        2712

SQL> ! ls -laSh /opt/oracle/product/23ai/dbhomeFree/dbs/TBS_SPECIAL.dbf
-rw-r-----. 1 oracle oinstall 2.7G Jan 10 08:42 /opt/oracle/product/23ai/dbhomeFree/dbs/TBS_SPECIAL.dbf

Ahora es mucho más sencillo el mantenimiento de nuestros tablespaces con el nuevo procedimiento que ha introducido Oracle.

¡Espero que os sirva!

[23ai] Shrink Tablespace

Subscribe to my newsletter

David Sanz

David Sanz