SQL para conocer el tamaño de los Tablespaces

En las actividades de monitoreo, uno de los puntos es revisar los tablespaces de la Base de Datos, para las BD Oracle, esto es importante ya que todos aquellos que no tienen una asignación automática de espacio, en caso de que se llene, comienzan los errores y en cierto punto una caída de la BD


También es útil conocer este dato, para llevar un estadístico de crecimiento, de forma que pueda uno solicitar los soportes (discos duros) o infraestructura necesaria tomando como referencia dicha estadistica.


El query que nos permitirá conocer estos datos es el siguiente:


SELECT trunc(sysdate) FECHA,
df.tablespace_name TABLESPACE,
df.total_space_mb "TOTAL MB",
(df.total_space_mb - fs.free_space_mb) "UTILIZADO MB",
fs.free_space_mb "LIBRE MB",
ROUND(100 * (fs.free_space / df.total_space),2) "% LIBRE"
FROM (SELECT tablespace_name, SUM(bytes) TOTAL_SPACE,
      ROUND(SUM(bytes) / 1048576) TOTAL_SPACE_MB
      FROM dba_data_files
      GROUP BY tablespace_name) df,
     (SELECT tablespace_name, SUM(bytes) FREE_SPACE,
       ROUND(SUM(bytes) / 1048576) FREE_SPACE_MB
       FROM dba_free_space
       GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name(+)
ORDER BY fs.tablespace_name;

El primer campo, que es la fecha, es para después poder hacer las comparativas de crecimiento entre periodos

Modificación, este query es el utilizado por el Enterprise Manager

SELECT trunc(sysdate) FECHA, d.status "Status", d.tablespace_name "Name", d.contents "Type", d.extent_management "Extent Management",
        TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)", 
TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024,'99999999.999') "Used (M)",
        TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990.00') "Used %"
        FROM sys.dba_tablespaces d, (select 
tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) a, (select 
tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) f WHERE 
d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+) AND NOT 
(d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY')
        UNION ALL
        SELECT trunc(sysdate) FECHA, d.status "Status", d.tablespace_name "Name", d.contents "Type",
        d.extent_management "Extent Management", TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)",
        TO_CHAR(NVL(t.bytes,0)/1024/1024,'99999999.999') ||'/'||TO_CHAR(NVL(a.bytes/1024/1024, 0),'99999999.999') "Used (M)", 
TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Used %" FROM sys.dba_tablespaces d, (select 
tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a, (select 
tablespace_name, sum(bytes_cached) bytes from v$temp_extent_pool group by tablespace_name) t WHERE 
d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+) AND 
d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY';


Ya si requieren ver a nivel de Datafile, este query es el indicado

SELECT dd.tablespace_name tablespace_name,
dd.file_name file_name,
dd.bytes/1024 TABLESPACE_KB,
SUM(fs.bytes)/1024 KBYTES_FREE,
MAX(fs.bytes)/1024 NEXT_FREE
FROM sys.dba_free_space fs, sys.dba_data_files dd
WHERE dd.tablespace_name = fs.tablespace_name
AND dd.file_id = fs.file_id
GROUP BY dd.tablespace_name, dd.file_name, dd.bytes/1024
ORDER BY dd.tablespace_name, dd.file_name;