Cómo crear y administrar un Tablespace en Oracle Database

By FrankDatabases, Oracle

Los tablespaces son una parte fundamental de la arquitectura de almacenamiento de Oracle Database. Permiten organizar lógicamente los datos, mientras que los datafiles representan el almacenamiento físico donde esos datos se guardan.

Para un DBA o administrador Oracle, crear un tablespace no consiste únicamente en ejecutar CREATE TABLESPACE. Antes debemos comprobar ubicación, capacidad disponible, configuración de los datafiles y necesidades de crecimiento.

En este artículo veremos los comandos más utilizados.

1. Tablespace vs. Datafile

La relación básica es:

Oracle Database
       │
       ├── Tablespace APP_DATA
       │        │
       │        ├── app_data01.dbf
       │        └── app_data02.dbf
       │
       └── Tablespace APP_INDEX
                │
                └── app_index01.dbf

El tablespace es lógico y los datafiles son físicos.

2. Consultar los tablespaces existentes

Antes de crear uno nuevo:

SELECT    tablespace_name,    status,    contents,    extent_management,    segment_space_managementFROM dba_tablespacesORDERBY tablespace_name;

Una consulta más sencilla:

SELECT tablespace_nameFROM dba_tablespacesORDERBY tablespace_name;

3. Consultar los datafiles existentes

SELECT    file_id,    tablespace_name,    file_name,    ROUND(bytes/1024/1024/1024,2) AS size_gb,    autoextensible,    ROUND(maxbytes/1024/1024/1024,2) AS max_gbFROM dba_data_filesORDERBY tablespace_name, file_id;

Esto también nos permite identificar el estándar utilizado para las rutas y nombres de archivos.

Por ejemplo:

/u01/app/oracle/oradata/PROD/users01.dbf/u01/app/oracle/oradata/PROD/app_data01.dbf

4. Crear un tablespace básico

Supongamos que necesitamos:

Tablespace: APP_DATADatafile:   /u01/app/oracle/oradata/PROD/app_data01.dbfTamaño:     10 GB

Podemos utilizar:

CREATE TABLESPACE APP_DATADATAFILE '/u01/app/oracle/oradata/PROD/app_data01.dbf'SIZE10G;

Verificamos:

SELECT    tablespace_name,    statusFROM dba_tablespacesWHERE tablespace_name ='APP_DATA';

5. Crear un tablespace con AUTOEXTEND

Una configuración más completa:

CREATE TABLESPACE APP_DATADATAFILE '/u01/app/oracle/oradata/PROD/app_data01.dbf'SIZE10GAUTOEXTEND ONNEXT1GMAXSIZE 50G;

Aquí:

SIZE 10G   │   └── Tamaño inicialAUTOEXTEND ON   │   └── Permite crecimiento automáticoNEXT 1G   │   └── Incrementos de 1 GBMAXSIZE 50G   │   └── Límite máximo del datafile

AUTOEXTEND puede ser muy útil, pero debemos controlar también la capacidad física disponible.

6. Crear un tablespace con administración local

En instalaciones Oracle modernas normalmente encontraremos locally managed tablespaces.

Por ejemplo:

CREATE TABLESPACE APP_DATADATAFILE '/u01/app/oracle/oradata/PROD/app_data01.dbf'SIZE10GAUTOEXTEND ONNEXT1GMAXSIZE 50GEXTENT MANAGEMENT LOCALSEGMENT SPACE MANAGEMENT AUTO;

SEGMENT SPACE MANAGEMENT AUTO permite que Oracle administre automáticamente el espacio libre dentro de los segmentos mediante ASSM.

7. Crear un tablespace en ASM

Si la base utiliza Oracle ASM, normalmente no trabajamos con una ruta tradicional como:

/u01/app/oracle/oradata/...

Podríamos utilizar un disk group:

CREATE TABLESPACE APP_DATADATAFILE '+DATA'SIZE10GAUTOEXTEND ONNEXT1GMAXSIZE 50G;

Con Oracle Managed Files, Oracle puede encargarse del nombre físico del datafile.

8. Crear un tablespace con varios datafiles

También podemos crear el tablespace inicialmente con varios archivos:

CREATE TABLESPACE APP_DATADATAFILE'/u01/app/oracle/oradata/PROD/app_data01.dbf'SIZE10G,'/u02/app/oracle/oradata/PROD/app_data02.dbf'SIZE10G;

Con AUTOEXTEND:

CREATE TABLESPACE APP_DATADATAFILE'/u01/app/oracle/oradata/PROD/app_data01.dbf'SIZE10G AUTOEXTEND ONNEXT1G MAXSIZE 50G,'/u02/app/oracle/oradata/PROD/app_data02.dbf'SIZE10G AUTOEXTEND ONNEXT1G MAXSIZE 50G;

9. Verificar el tablespace después de crearlo

SELECT    tablespace_name,    status,    contents,    extent_management,    segment_space_managementFROM dba_tablespacesWHERE tablespace_name ='APP_DATA';

Después verificamos sus datafiles:

SELECT    file_id,    file_name,    ROUND(bytes/1024/1024/1024,2) AS size_gb,    autoextensible,    ROUND(maxbytes/1024/1024/1024,2) AS max_gbFROM dba_data_filesWHERE tablespace_name ='APP_DATA';

10. Agregar otro datafile posteriormente

Supongamos que APP_DATA está creciendo.

No necesitamos crear otro tablespace. Podemos agregar capacidad:

ALTER TABLESPACE APP_DATAADD DATAFILE'/u01/app/oracle/oradata/PROD/app_data02.dbf'SIZE10G;

Con autoextend:

ALTER TABLESPACE APP_DATAADD DATAFILE'/u01/app/oracle/oradata/PROD/app_data02.dbf'SIZE10GAUTOEXTEND ONNEXT1GMAXSIZE 50G;

En ASM:

ALTER TABLESPACE APP_DATAADD DATAFILE '+DATA'SIZE10GAUTOEXTEND ONNEXT1GMAXSIZE 50G;

11. Aumentar un datafile existente

Otra posibilidad es ampliar el archivo existente:

ALTER DATABASE DATAFILE'/u01/app/oracle/oradata/PROD/app_data01.dbf'RESIZE 20G;

O modificar su autoextend:

ALTER DATABASE DATAFILE'/u01/app/oracle/oradata/PROD/app_data01.dbf'AUTOEXTEND ONNEXT1GMAXSIZE 50G;

Por lo tanto, ante un problema de espacio tenemos al menos dos opciones:

Tablespace casi lleno        │        ├── Resize existing datafile        │        ├── Increase AUTOEXTEND limit        │        └── Add new datafile

La decisión depende de la arquitectura y de la capacidad disponible.

12. Consultar el espacio libre

SELECT    tablespace_name,    ROUND(SUM(bytes)/1024/1024/1024,2) AS free_gbFROM dba_free_spaceGROUPBY tablespace_nameORDERBY tablespace_name;

Para uno específico:

SELECT    tablespace_name,    ROUND(SUM(bytes)/1024/1024/1024,2) AS free_gbFROM dba_free_spaceWHERE tablespace_name ='APP_DATA'GROUPBY tablespace_name;

13. Identificar qué objetos consumen más espacio

Antes de aumentar capacidad, debemos entender por qué está creciendo.

SELECT    owner,    segment_name,    segment_type,    ROUND(bytes/1024/1024/1024,2) AS size_gbFROM dba_segmentsWHERE tablespace_name ='APP_DATA'ORDERBY bytes DESCFETCHFIRST20ROWSONLY;

Esto podría revelar:

TABLEINDEXLOBSEGMENTLOBINDEXTABLE PARTITIONINDEX PARTITION

Un crecimiento inesperado podría deberse a una carga masiva, índices, LOBs, retención incorrecta o procesos batch.

14. Asignar el tablespace a un usuario

Podemos establecerlo como tablespace por defecto:

ALTERUSER APP_USERDEFAULT TABLESPACE APP_DATA;

También debemos gestionar su cuota según la política de la organización:

ALTERUSER APP_USERQUOTA 10G ON APP_DATA;

Consultar:

SELECT    username,    default_tablespace,    temporary_tablespaceFROM dba_usersWHERE username ='APP_USER';

15. Crear un Temporary Tablespace

Aquí existe una diferencia importante.

Para TEMP utilizamos:

CREATETEMPORARY TABLESPACE APP_TEMPTEMPFILE'/u01/app/oracle/oradata/PROD/app_temp01.dbf'SIZE10GAUTOEXTEND ONNEXT1GMAXSIZE 30G;

Observa:

Permanent Tablespace        ↓DATAFILETemporary Tablespace        ↓TEMPFILE

Los tempfiles se consultan mediante:

SELECT    tablespace_name,    file_name,    ROUND(bytes/1024/1024/1024,2) AS size_gb,    autoextensibleFROM dba_temp_files;

16. Poner un tablespace OFFLINE / ONLINE

ALTER TABLESPACE APP_DATA OFFLINE;

Para regresarlo:

ALTER TABLESPACE APP_DATA ONLINE;

Estas operaciones afectan la disponibilidad de los objetos almacenados allí, por lo que deben realizarse con mucho cuidado en producción.

17. Cambiar a READ ONLY

ALTER TABLESPACE APP_DATA READONLY;

Regresar a lectura/escritura:

ALTER TABLESPACE APP_DATA READWRITE;

Podemos comprobarlo:

SELECT    tablespace_name,    statusFROM dba_tablespacesWHERE tablespace_name ='APP_DATA';

18. Eliminar un tablespace

Para eliminar únicamente la definición:

DROP TABLESPACE APP_DATA;

Para eliminar también su contenido:

DROP TABLESPACE APP_DATAINCLUDING CONTENTS;

Y existe:

DROP TABLESPACE APP_DATAINCLUDING CONTENTS AND DATAFILES;

Muchísimo cuidado con este último comando.

INCLUDING CONTENTS AND DATAFILES puede eliminar tanto los objetos como los archivos asociados.

No debería ejecutarse en producción sin validar exactamente qué contiene el tablespace, dependencias, backups y Change Management.

19. Comandos que deberías memorizar

Si estás comenzando con administración Oracle, estos son buenos candidatos:

SELECT tablespace_nameFROM dba_tablespaces;SELECT*FROM dba_data_files;SELECT*FROM dba_temp_files;

Crear:

CREATE TABLESPACE APP_DATADATAFILE '/u01/oradata/PROD/app_data01.dbf'SIZE10GAUTOEXTEND ONNEXT1GMAXSIZE 50G;

Agregar capacidad:

ALTER TABLESPACE APP_DATAADD DATAFILE '/u01/oradata/PROD/app_data02.dbf'SIZE10GAUTOEXTEND ONNEXT1GMAXSIZE 50G;

Resize:

ALTER DATABASE DATAFILE'/u01/oradata/PROD/app_data01.dbf'RESIZE 20G;

Cambiar autoextend:

ALTER DATABASE DATAFILE'/u01/oradata/PROD/app_data01.dbf'AUTOEXTEND ONNEXT1GMAXSIZE 50G;

20. Flujo recomendado en producción

Supongamos que recibimos una alerta:

“APP_DATA tablespace is 95% full.”

No deberíamos agregar inmediatamente otro datafile.

Primero podemos investigar:

Tablespace 95% full        ↓¿Es realmente un problema de capacidad?        ↓Revisar tamaño y espacio libre        ↓¿AUTOEXTEND está habilitado?        ↓Revisar MAXSIZE        ↓¿Hay espacio en filesystem / ASM?        ↓Identificar segmentos grandes        ↓¿El crecimiento es normal?        ↓¿Carga masiva / índice / LOB / batch?        ↓Revisar tendencia de crecimiento        ↓Determinar acción       ↙ ↓ ↘ RESIZE  AUTOEXTEND  ADD DATAFILE        ↓Verificar        ↓Monitoring        ↓Capacity Planning / RCA

Antes de crear un tablespace

Una buena práctica es comprobar al menos:

✓ ¿El tablespace realmente es necesario?✓ ¿Cuál es el estándar de nombres?✓ ¿Filesystem, ASM u Oracle Managed Files?✓ ¿Cuánto espacio físico existe?✓ ¿Cuál debe ser el tamaño inicial?✓ ¿Necesitamos AUTOEXTEND?✓ ¿Cuál debe ser MAXSIZE?✓ ¿Qué usuarios utilizarán el tablespace?✓ ¿Cuál será su patrón de crecimiento?✓ ¿Está incluido correctamente en la estrategia de backup?

Crear un tablespace es sencillo:

CREATE TABLESPACE ...

La parte importante para un administrador Oracle es diseñar correctamente su capacidad, crecimiento, ubicación, monitoreo y recuperación.

En producción, el objetivo no debería ser esperar a recibir:

ORA-01653Unable to extend table...

para comenzar a pensar en almacenamiento.

Una administración madura utiliza monitoring y capacity planning para detectar el crecimiento antes de que se convierta en un incidente.

¿Qué verificas primero cuando un tablespace de Oracle alcanza el 90% de utilización?

Deja un comentario

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