Indicamos para qué sirve el tablespace SYSTEM de Oracle y algunos consejos para su correcto uso. Explicamos algunos casos en los que el tablespace SYSTEM de Oracle puede crecer en exceso y llegar a llenarse por completo, generando problemas en la base de datos Oracle. Usamos la versión 11g de Oracle sobre Linux Red Hat.
- Tablespace SYSTEM de Oracle.
- ¿Por qué puede llenarse el tablespace SYSTEM de Oracle?.
- Algunas consultas SQL de tablas y vistas útiles para obtener información del uso del tablespace SYSTEM en Oracle.
- Solución a posibles problemas de uso de espacio en tablespace SYSTEM de Oracle.
Tablespace SYSTEM de Oracle
El tablespace SYSTEM de Oracle, necesario en cualquier instalación, contiene el diccionario de datos de todos los objetos de la base de datos.
Dicho tablespace siempre estará online una vez que se inicie la base de datos. Es necesario para su funcionamiento.
No se recomienda usar este tablespace para almacenar datos, únicamente para uso del sistema. Para nuestras tablas y aplicaciones crearemos un tablespace (o varios) adicionales.
El tablespace SYSTEM contiene la información de los usuarios del sistema SYS y SYSTEM. Dichos usuarios contendrán la información necesaria para que funcione todo el sistema: datos de los objetos que se creen con el resto de usuarios, auditorías, estadísticas, etc..
Uno de los motivos por lo que se recomienda encarecidamente no usar el tablespace SYSTEM para almacenar los datos de nuestras aplicaciones es, precisamente, porque si se llena o si alguna aplicación hace un uso indebido y lo bloquea, se bloqueará todo el sistema Oracle. Por ello, siempre es recomendable dejar este tablespace únicamente para uso del sistema y crear nosotros los que necesitemos para las aplicaciones.
En el caso en que no se tenga tablespace temporal por defecto, o bien se elimine, Oracle usará el tablespace SYSTEM como temporal. Por supuesto es no es nada recomendable, siempre debe haber un tablespace temporal independiente.
¿Por qué puede llenarse el tablespace SYSTEM de Oracle?
Existen varios motivos por los que el tablespace SYSTEM puede llenarse, generando problemas de rendimiento en la base de datos Oracle:
- Si lo usamos para almacenar los datos de las aplicaciones: como hemos comentado anteriormente, si usamos el tablespace SYSTEM para almacenar los datos de nuestras aplicaciones correremos el riesgo de que se llene, deteniéndose la base de datos.
- Las tablas de auditorías AUD$ y FGA_LOG$ crecen en exceso: cuando tenemos activadas las estadísticas y auditorías en Oracle, en función de las políticas de retención, podrían generar que las tablas AUD$ y FGA_LOG$ aumentaran de tamaño, llegando a dejar sin espacio el tablespace SYSTEM.
- Crecimiento del número de objetos: si nuestra base de datos está creciendo a un ritmo elevado en cuanto a número de tablas, de índices y otros objetos, la información del diccionario de datos de estos objetos se almacena en el tablespace SYSTEM, por lo que puede aumentar su tamaño en poco tiempo. Por ejemplo, imaginemos que hemos instalado una nueva aplicación en nuestra organización que requiere de cientos de tablas, índices, triggers y otros objetos. Al crear el tablespace correspondiente con sus objetos, toda la información básica (diccionario de datos) de estas tablas se almacenará en el SYSTEM, aumentando su tamaño.
Como vemos los motivos del crecimiento del tablespace SYSTEM pueden ser variados. Por ello conviene conocer algunas tablas y vistas que nos ayudarán a ver el origen exacto del crecimiento del tablespace SYSTEM, para así poder obrar en consecuencia.
Algunas consultas SQL de tablas y vistas útiles para obtener información del uso del tablespace SYSTEM en Oracle
Para ejecutar las consultas SQL y de alteración de datos hemos usado Oracle SQL Developer, un software de Oracle gratuito y disponible en su web oficial.
Para comprobar el estado del tablespace SYSTEM:
|
1 2 3 |
select tablespace_name, status from dba_tablespaces where TABLESPACE_NAME='SYSTEM'; |
Siempre que la base de datos esté iniciada y en estado correcto devolverá «ONLINE».
Consulta SQL que devuelve el espacio usado (en Mb) y el espacio total:
|
1 2 3 4 5 6 7 8 9 10 |
select f.tn Tablespace, round(usado/1048576,2) "Usado", round(total/1048576,2) "Total" from (select tablespace_name tn, sum(nvl(bytes,0)) usado from dba_segments group by tablespace_name) s, (select tablespace_name tn, sum(nvl(bytes,0)) total from dba_data_files group by tablespace_name) f where s.tn=f.tn and f.tn='SYSTEM'; |
Como vemos en el ejemplo anterior, el tablespace SYSTEM tiene unos 15Gb de espacio y tiene ocupados 14,9Gb.
La siguiente consulta es muy interesante porque devuelve el tamaño usado por objeto en el tablespace SYSTEM:
|
1 2 3 4 5 6 |
select * from (select owner,segment_name||'~'||partition_name segment_name,bytes/(1024*1024) Espacio_Mb from dba_segments where tablespace_name = 'SYSTEM' order by BLOCKS desc) where rownum < 40; |
Como vemos en el ejemplo anterior, el objeto «AUD$» está ocupando gran parte del espacio del tablespace SYSTEM.
Para comprobar el número de objetos total de nuestra base de datos Oracle podemos ejecutar la siguiente consulta SQL:
|
1 2 |
select count(*) from USER_OBJECTS; |
Podremos monitorizar (por ejemplo con Pandora FMS) el número de objetos de la tabla anterior y ver su crecimiento. Si en algún momento es muy grande, también puede crecer el tamaño usado del tablespace SYSTEM.
Solución a posibles problemas de uso de espacio en tablespace SYSTEM de Oracle
A continuación mostraremos cómo solucionar el problema que surge cuando el tablespace SYSTEM de Oracle se llena o está próximo a llenarse. Explicaremos varios métodos el función de la causa.
Antes de intentar aplicar alguna de las soluciones que mostramos a continuación es MUY RECOMENDABLE hacer una copia de seguridad de la base de datos y del servidor donde esté alojada.
Solución al llenado de espacio en tablespace SYSTEM de Oracle por tablas de aplicaciones
Si hemos usado el tablespace SYSTEM para albergar las tablas y objetos de nuestras aplicaciones, podremos encontrarnos con el problema de que acabe llenándose. En este caso tenemos dos opciones:
- Ampliar el tamaño del tablespace SYSTEM: no recomendable.
- Mover los datos de las aplicaciones (tablas y resto de objetos) a otro tablespace: recomendable.
Esta última opción, sin duda la recomendable, se puede realizar de varias formas. La más rápida, sin usar aplicativos de terceros, la explicamos a continuación.
Suponemos que el usuario usuario_origen es el empleado para el acceso a las tablas del tablespace SYSTEM.
En primer lugar se crearía un tablespace (por ejemplo TBL_APP) y un nuevo usuario (usuario_destino) con su esquema en el tablespace creado. Se usará para albergar los datos de la aplicación.
Se crea una carpeta de destino de la exportación:
|
1 2 3 4 |
CREATE DIRECTORY dir_expt AS '/ruta_fichero/exportacion'; GRANT WRITE ON DIRECTORY dir_expt TO usuario_origen; GRANT READ, WRITE ON DIRECTORY dir_expt TO usuario_destino; GRANT CREATE TABLE TO usuario_destino; |
A continuación se exportan (expdp), del tablespace SYSTEM, únicamente de los objetos de las aplicaciones (del usuario usuario_origen):
|
1 2 3 4 5 |
expdp usuario_origen/contraseña directory=dir_expt tables=usuario_origen.tabla_1,usuario_origen.tabla_2, usuario_origen.tabla_3 dumpfile=exporacion_origen.dmp logfile=exportacion_origen.log |
Tras la exportación se hace una importación, cambiando el usuario destino:
|
1 2 3 4 5 6 7 |
impdp usuario_destino/contraseña directory=dir_expt tables=usuario_origen.tabla_1,usuario_origen.tabla_2, usuario_origen.tabla_3 remap_schema usuario_origen:usuario_destino remap_tablespace=SYSTEM:TBL_APP dumpfile=exportacion_origen.dmp logfile=importacion_origen.log |
En el ejemplo anterior exportamos las tablas del usuario «usuario_origen» del tablespace SYSTEM al tablespace «TBL_APP» del usuario «usuario_destino». Lógicamente cambiaremos «tabla_1» «tabla_2», «tabla_n» por el nombre de las tablas de nuestro usuario.
En la aplicaciones, se usará el nuevo usuario (usuario_destino) para que tengan acceso a las tablas de su esquema.
En cuanto se haya verificado que todo funciona correctamente podremos eliminar las tablas del tablespace SYSTEM del usuario origen (usuario_origen), previa copia de seguridad.
Se pueden usar también aplicaciones de terceros para hacer este paso de datos o bien el propio SQL Developer de Oracle.
Solución al llenado de espacio en tablespace SYSTEM de Oracle por crecimiento de objetos
Si nuestra base de datos está creciendo porque crece el número de tablas, índices, vistas, triggers y otros objetos, también crecerá el espacio usado en el tablespace SYSTEM. Si el crecimiento es muy grande, por ejemplo por la instalación de una nueva aplicación que tiene muchos objetos, el tablespace SYSTEM podría llenarse por completo. La única solución en este caso es ampliar el tamaño del tablespace SYSTEM, aunque no es recomendable ampliarlo en exceso, intentaremos hacerlo ajustándonos a lo necesario.
Antes de continuar conviene indicar que el tablespace SYSTEM, en una instalación de Oracle 11g por defecto, viene configurado para que incremente su tamaño de forma automática a 10mb cuando lo necesite:
Aún así, si queremos ampliarlo de forma manual, podremos o bien ampliar su archivo de datos o bien añadirle un nuevo archivo de datos. Para aumentar el tamaño del tablespace SYSTEM amplienado su archivo de datos existente seguiremos estos pasos. En primer lugar tendremos que saber el fichero de datos (ruta y nombre) actual que queremos ampliar, podemos obtenerlo con la siguiente consulta SQL:
|
1 2 3 4 |
SELECT DISTINCT sgm.TABLESPACE_NAME, dtf.FILE_NAME FROM DBA_SEGMENTS sgm JOIN DBA_DATA_FILES dtf ON (sgm.TABLESPACE_NAME = dtf.TABLESPACE_NAME) WHERE sgm.TABLESPACE_NAME = 'SYSTEM'; |
En cuanto sepamos el nombre y la ruta del fichero de datos del tablespace SYSTEM lo podríamos ampliar ejecutando:
|
1 |
alter database datafile '/ruta/nombre_fichero.dbf' resize 1g; |
Donde «/ruta/nombre_fichero.dbf» será el nombre del fichero del tablespace SYSTEM obtenido anteriormente.
En el caso anterior ampliaríamos el fichero elegido a 1Gb.
La segunda forma de ampliar el espacio de un tablespace es añadiéndole un nuevo fichero de datos, con el comando:
|
1 |
alter tablespace SYSTEM add datafile '/ruta/nombre_nuevo_fichero_datos.dbf' size 1g; |
En el caso anterior debemos conocer la ubicación de los archivos de datos del tablespace SYSTEM, para colocar el nuevo en la misma ubicación. Esto podemos hacerlo ejecutando la consulta anterior que nos devolvía los ficheros de datos del tablespace SYSTEM y su ubicación.
Solución al llenado de espacio en tablespace SYSTEM de Oracle por tabla/vista AUD$
Si hemos comprobado que la tabla AUD$ está llenando el tablespace SYSTEM, con la consulta:
|
1 2 3 4 5 6 |
select * from (select owner,segment_name||'~'||partition_name segment_name,bytes/(1024*1024) Espacio_Mb from dba_segments where tablespace_name = 'SYSTEM' order by BLOCKS desc) where rownum < 40; |
En este caso, debemos proceder con cierta precaución, esta vista suele tener un gran crecimiento se nuestra base de datos es muy usada (muchos usuarios, muchas tablas, muchas transacciones) y si tenemos políticas de retención altas para las auditorías y estadísticas.
Existen varios parámetros para establecer el funcionamiento de estas estadísticas, podremos consultarlos ejecutando:
|
1 2 |
SELECT * FROM dba_audit_mgmt_config_params; |
Los parámetros que obtendrá y su valor por defecto:
- DB AUDIT TABLESPACE: SYSAUX.
- DB AUDIT TABLESPACE: SYSAUX.
- AUDIT FILE MAX SIZE: 10000.
- AUDIT FILE MAX SIZE: 10000.
- AUDIT FILE MAX AGE: 5.
- AUDIT FILE MAX AGE: 5.
- DB AUDIT CLEAN BATCH SIZE: 10000.
- DB AUDIT CLEAN BATCH SIZE: 10000.
- OS FILE CLEAN BATCH SIZE: 1000.
- OS FILE CLEAN BATCH SIZE: 1000.
Mover tablas AUD$ del tablespace SYSTEM a otro tablespace
En el caso en que el crecimiento de esta tabla (o de otras similares) sea muy grande podremos moverla a otro tablespace, como indicamos a continuación.
En primer lugar crearemos un nuevo tablespace, con el comando:
|
1 2 3 4 5 |
CREATE SMALLFILE TABLESPACE "AUD" LOGGING DATAFILE '/ruta_ficheros/nombre_fichero_aud.DBF' SIZE 15g REUSE AUTOEXTEND ON NEXT 1024M MAXSIZE 30g EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; |
En el ejemplo de creación de tablespace anterior le estamos indicando que cree un fichero de datos (debe estar en la ubicación del resto de ficheros de datos de Oracle, con un nombre y un espacio inicial de 15Gb. Le hemos indicado que crezca automáticamente 1GB, hasta un máximo de 30Gb.
Para poder mover las tablas AUD$ y FGA_LOG$ del tablespace SYSTEM al nuevo creado «AUD» deberemos usar un procedimiento almacenado, en concreto DBMS_AUDIT_MGMT.set_audit_trail_location. Para ello ejecutaremos lo siguiente:
|
1 2 3 4 5 6 |
BEGIN DBMS_AUDIT_MGMT.set_audit_trail_location( audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD, audit_trail_location_value => 'AUD'); END; / |
En el procedimiento anterior deberemos indicar, en audit_trail_location_value, el nombre del tablespace creado.
Si la ejecución ha sido correcta, podremos volver a ejecutar la siguiente consulta SQL para verificar que ahora la tabla AUD$ se encuentra en el tablespace AUD en lugar de en el SYSTEM:
|
1 2 3 4 |
SELECT table_name, tablespace_name FROM dba_tables where table_name in ('AUD$', 'FGA_LOG$') ORDER BY table_name; |
