Reducir el tamaño de tablespace BIGFILE en Oracle 23ai

Hace unas semanas, en el artículo "Tablespaces BIGFILE por Defecto en Oracle 23ai" discutimos un cambio en Oracle Database 23ai, donde los tablespaces creados por los usuarios son por defecto de tipo BIGFILE. Ahora vamos a ver una importante mejora en Oracle 23ai que nos permite reducir el tamaño de los tablespaces BIGFILE cuando se ha liberado espacio en los mismo, por más que el espacio no esté al final del tablespace, como ocurría anteriormente.

Preparación del Escenario

Llenando el Tablespace

En este caso, nos conectamos a una base de datos Oracle 23ai y creamos un tablespace de tipo BIGFILE con la siguiente sintaxis:

CREATE TABLESPACE SHRINKTBSP DATAFILE 'SHRINK_.dbf' SIZE 10M AUTOEXTEND ON;

Como explicamos en el articulo mencionado anteriormente, en Oracle 23ai no es necesario especificar BIGFILE al momento de crear tablespace ya que por defecto es el nuevo formato. Esto lo podemos validar rápidamente con la siguiente consulta:

SELECT t.tablespace_name, t.bigfile, d.blocks
FROM dba_tablespaces t JOIN dba_data_files d ON d.tablespace_name = t.tablespace_name
WHERE t.tablespace_name = 'SHRINKTBSP';

Podemos ver que el tamaño del tablespace es de 1280 bloques de 8 kilobytes. 

A continuación, vamos a crear una tabla "t1" con un tamaño considerable, para ello haremos un producto cartesiano sobre la vista DBA_SEGMENTS del diccionario de datos:

CREATE TABLE t1 TABLESPACE SHRINKTBSP AS
SELECT s.* FROM dba_segments s CROSS JOIN dba_segments x;

Esta tabla ocupa aproximadamente 2,5 gigabytes. Luego creamos una segunda tabla llamada "t2" con un script similar pero con un tamaño menor, cercano a los 200 megabytes:

CREATE TABLE t2 TABLESPACE SHRINKTBSP AS
SELECT s.* FROM dba_segments s CROSS JOIN dba_sequences q;

Una vez creadas ambas tablas, vamos a consultar nuevamente el tamaño total del tablespace (agregamos al número de bloques el tamaño en megabytes):

SELECT t.tablespace_name, t.bigfile, d.blocks, ROUND(d.bytes / 1024 / 1024 ) AS MBytes
FROM dba_tablespaces t JOIN dba_data_files d ON d.tablespace_name = t.tablespace_name
WHERE t.tablespace_name = 'SHRINKTBSP';

Podemos ver que el tablespace creció de 10 megabytes a más de 3000. De ese espacio, podemos ver cuanto se encuentra libre con la siguiente sentencia:

SELECT f.tablespace_name, ROUND(f.bytes / 1024 / 1024 ) AS MBytes
FROM dba_free_space f
WHERE f.tablespace_name = 'SHRINKTBSP';

Rápidamente podemos confirmar que de los 3000 MB del tablespace, sólo algo mas de 50 se encuentran libres y el resto ocupado.

Liberando Espacio en el Tablespace

A continuación, vamos a liberar espacio en el tablespace al truncar la tabla "t1" (la cual fue creada primero):

TRUNCATE TABLE t1;

Esta operación elimina los datos de la tabla "t1", dejando unos 2500 megabytes de espacio libre en el medio del datafile. Podemos verificar esto ejecutando nuevamente las consultas que nos muestran el tamaño del tablespace (que no varió) y el espacio libre en el mismo:

Vemos que ahora tenemos seis chunks de espacio libre en vez de dos, los cuales suman más de 2500 megabytes.

Y por último, corroboramos a nivel sistema operativo el tamaño del datafile:

Achicando el Tablespace con SHRINK

Estimando el Espacio Libre

El primer paso para "recuperar" ese espacio libre y achicar el tablespace, es estimar el espacio que puede ser recuperado. Para ello utilizamos la siguiente sentencia:

execute dbms_space.shrink_tablespace('SHRINKTBSP',shrink_mode => DBMS_SPACE.TS_MODE_ANALYZE);

El informe nos indica que el tablespace tiene un único objeto que ocupa 0,2 gigabytes que es movible, esto significa que pueden ser movidos desde el final del archivo al principio del mismo, para luego poder achicar el tablespace. El tamaño actual es de 2.5 gigabytes, y el tamaño estimado final puede ser reducido a menos de medio gigabyte (0,46).

Esto en la práctica implica mover todos los datos de la tabla "t2" del lugar actual en el tablespace al lugar que quedó libre al truncar la tabla "t1", achicando luego el tablespace. Si hubiera objetos que no pueden ser movidos, eso es informado en este paso.

Moviendo los Objetos y Achicando el Tablespace

Para mover los objetos y reducir el tamaño del tablespace, debemos ejecutar la siguiente sentencia:

execute dbms_space.shrink_tablespace('SHRINKTBSP');

A diferencia de la estimación, la cual es una tarea muy rápida que llevó menos de un segundo, el reducir el tablespace implica mover los datos desde el final del datafile al comienzo del mismo, por lo que puede requerir un tiempo de ejecución más elevado.

Podemos ver que el proceso movió unos 200 megabytes de datos desde el final del archivo al comienzo del mismo, y luego redujo en tamaño del archivo pasando de 3 gigabytes a unos 300 megabytes, lo cual confirmamos con el mismo script que usábamos para ver el tamaño del tablespaces:

Como así también a nivel sistema operativo:

Consideraciones

Tablespace SYSAUX

Como en Oracle Database 23ai el tablespace SYSAUX es de tipo BIGFILE, ahora podemos reducir su tamaño en forma sencilla luego de depurar tablas, sin necesidad de mover los objetos entre tablespaces.

Online vs Offline

Por defecto el procedimiento trata de mover todos los objetos ONLINE. Si alguno de los objetos no lo permite, esto genera un error. Usando la opción "TS_MODE_SHRINK_FORCE" podemos hacer que si una operación de mover datos falla en modo online, el proceso reintente la misma en forma offline.

Documentación

Recomendamos leer la documentación sobre el procedimiento DBMS_SPACE.SHRINK_TABLESPACE para entender todas las características de su funcionamiento.