Monitoring tablespace capacity

Running out of tablespace is one of the most common Oracle emergencies. Queries that track tablespace usage should be part of every DBA's toolkit and can be automated with alerts.

Recipe #1 - Tablespace usage summary

Get an overview of all tablespaces with used, free, and percentage used:

SELECT ts.tablespace_name,
       ROUND(ts.total_mb, 2)        AS total_mb,
       ROUND(ts.total_mb - fs.free_mb, 2) AS used_mb,
       ROUND(fs.free_mb, 2)         AS free_mb,
       ROUND((1 - fs.free_mb / ts.total_mb) * 100, 1) AS pct_used
FROM (
  SELECT tablespace_name, SUM(bytes) / 1024 / 1024 AS total_mb
  FROM dba_data_files
  GROUP BY tablespace_name
) ts
JOIN (
  SELECT tablespace_name, SUM(bytes) / 1024 / 1024 AS free_mb
  FROM dba_free_space
  GROUP BY tablespace_name
) fs ON ts.tablespace_name = fs.tablespace_name
ORDER BY pct_used DESC;

Recipe #2 - Find tablespaces that are running low

Alert on tablespaces with less than 10% free space:

SELECT tablespace_name,
       ROUND(used_pct, 1) AS pct_used,
       ROUND(free_mb, 2) AS free_mb
FROM (
  SELECT ts.tablespace_name,
         (1 - NVL(fs.free_mb, 0) / ts.total_mb) * 100 AS used_pct,
         NVL(fs.free_mb, 0) AS free_mb
  FROM (
    SELECT tablespace_name, SUM(bytes) / 1024 / 1024 AS total_mb
    FROM dba_data_files GROUP BY tablespace_name
  ) ts
  LEFT JOIN (
    SELECT tablespace_name, SUM(bytes) / 1024 / 1024 AS free_mb
    FROM dba_free_space GROUP BY tablespace_name
  ) fs ON ts.tablespace_name = fs.tablespace_name
)
WHERE used_pct > 90
ORDER BY used_pct DESC;

Recipe #3 - Find largest tables by segment size

When a tablespace is full, you need to know what's consuming the space:

SELECT owner, segment_name, segment_type,
       ROUND(bytes / 1024 / 1024, 2) AS size_mb
FROM dba_segments
WHERE tablespace_name = 'USERS'
ORDER BY bytes DESC
FETCH FIRST 20 ROWS ONLY;