Conocer el espacio libre en BBDD Oracle

Las aplicaciones de monitorización nos avisan cuando se están llenando los ficheros. Son unas herramientas de gran utilidad para realizar la administración de forma eficiente.

También puede ser recomendable hacer este seguimiento por medio de querys directamente sobre la misma BBDD.

Ver espacio por tablespaces

Los objetos de la BBDD, tales como las tablas, los índices, etc. se almacenan en tablespaces. Una vez que se crea un objeto y se almacena en el tablespace, a medida que van entrando datos a dicho objeto, es el motor de Oracle el encargado de asignar el espacio automaticamente dentro del tablespace.

Por esta razón, es muy interesante poder monitorizar el espacio libre en BBDD Oracle a nivel de tablespace, y tenemos una query que viene muy bien para realizar esta labor.

select df.tablespace_name nb_tablespace,
       round(sum(df.bytes) / 1024 / 1024) espacio_total_MB,
       round(free.bytes/ / 1024 / 1024) espacio_libre_MB,
       round(100 * (free.bytes / round(sum(df.bytes))),2) Porcentaje_Libre
From dba_data_files df
     join (select tablespace_name, sum(bytes) bytes 
           From dba_free_space 
           group by tablespace_name) free on df.tablespace_name=free.tablespace_name
group by df.tablespace_name, free.bytes

Esta query devuelve los tablespaces, con el tamaño del mismo, así como el espacio libre y el procentaje de espacio libre, para todos los tablespaces de la instancia.
Se puede monitorizar solamente un tablespace, añadiendo el filtro con la clausula WHERE y el nombre del tablespace.

where df.tablespace_name = 'nb_tablespace'

Es recomendable ordenar el resultado de la query. Las aplicaciones de monitorización normalmente mantienen umbrales de alertas basandose en el porcentaje libre del tablespace.

Para buscar el mismo efecto, se puede ordenar el resultado de la query por la columna que muestra el porcentaje de espacio libre, con la clausula ORDER BY.

order by 4

Espacio libre por datafiles

Un tablespace está compuesto por uno o más datafiles.

Es por ello, que además de revisar el espacio libre y ocupado de los tablespaces, es interesante también revisar el nivel de ocupación de los ficheros que componen el tablespace.

Por defecto, los ficheros de datos se van llenando uniformemente, para evitar que unos ficheros estén más ocupados que otros, pero a lo largo de la vida de la BBDD se pueden crear nuevos ficheros o ampliar los existentes, con lo que en un momento determinado no es obligatorio que estén con la misma ocupación.

Por eso puede ser interesante también conocer el uso y espacio libre de cada uno de los datafiles de la BBDD Oracle.
La query para conocer estos datos es:

select tablespace_name nb_tablespace, 
       file_id, file_name nb_fichero, 
       size_data_file_mb tamaño_fichero_MB,
       nvl(free_size_mb,0) espacio_libre_MB
from (
      select d.tablespace_name, d.file_id, d.file_name, 
             d.bytes/1024/1024 size_data_file_mb, 
             sum(f.bytes)/1024/1024 free_size_mb 
      from dba_data_files d left outer join dba_free_space f on d.file_id=f.file_id
      group by d.tablespace_name, d.file_id, d.file_name, d.bytes
      )
order by tablespace_name, file_name;

Esta query devuelve todos los datafiles de la instancia. Se puede filtrar por un tablespace en concreto, poniendo un filtro con WHERE

where d.tablespace_name = 'USERS'

Con estas dos querys podemos tener controlado el crecimiento de los datos en nuestra BBDD.
Esperamos que os haya sido de utilidad.

Un saludo.
Equipo de base de datos

Si no quieres perderte trucos como este y estar al día de este mundo, suscríbete a nuestra newsletter. Te mantendremos informados con un solo email al mes.

¿Aún no conoces Query Performance? Descubre cómo puede ayudarte en tu entorno Oracle. Más información en su página de LinkedIn.

Sígue a GPS en LinkedIn