How to Monitor Oracle Tablespace Usage & Free Space

How to Monitor Oracle Tablespace Usage and Free Space

Running out of tablespace is one of the most common — and avoidable — Oracle incidents. A tablespace at 100% capacity causes ORA-01653 (unable to extend table) or ORA-01652 (unable to extend temp segment) and brings applications down. Proactive monitoring catches growth before it becomes an outage. This guide covers queries from simple free-space checks to automated alerting.

Step 1: Quick Tablespace Usage Check

This is the query most DBAs run first thing every morning.

-- Current tablespace usage overview (all permanent tablespaces)
SELECT
  t.tablespace_name,
  t.status,
  ROUND(SUM(d.bytes)/1024/1024/1024, 2)           AS total_gb,
  ROUND(NVL(SUM(f.free_bytes),0)/1024/1024/1024, 2) AS free_gb,
  ROUND((1 - NVL(SUM(f.free_bytes),0)/SUM(d.bytes)) * 100, 1) AS used_pct,
  t.contents
FROM dba_tablespaces t
JOIN dba_data_files d   ON t.tablespace_name = d.tablespace_name
LEFT JOIN (
  SELECT tablespace_name, SUM(bytes) AS free_bytes
  FROM dba_free_space
  GROUP BY tablespace_name
) f ON t.tablespace_name = f.tablespace_name
GROUP BY t.tablespace_name, t.status, t.contents
ORDER BY used_pct DESC;

Step 2: Include Autoextend Potential

Autoextend means a tablespace can grow beyond its current size — the “real” capacity includes the MAXSIZE of all datafiles.

-- Tablespace usage including autoextend capacity
SELECT
  t.tablespace_name,
  ROUND(current_size_mb / 1024, 2)   AS current_gb,
  ROUND(free_mb / 1024, 2)           AS free_gb,
  ROUND(max_size_mb / 1024, 2)       AS max_gb,
  ROUND(free_mb / current_size_mb * 100, 1) AS pct_free_current,
  ROUND((max_size_mb - (current_size_mb - free_mb)) / max_size_mb * 100, 1) AS pct_free_max,
  CASE
    WHEN autoextend_count > 0 AND max_size_mb > current_size_mb
      THEN 'YES - can grow to ' || ROUND(max_size_mb/1024, 1) || ' GB'
    ELSE 'NO'
  END AS can_autoextend
FROM (
  SELECT
    d.tablespace_name,
    SUM(d.bytes/1024/1024)    AS current_size_mb,
    SUM(
      CASE WHEN d.autoextensible = 'YES'
        THEN d.maxbytes/1024/1024
        ELSE d.bytes/1024/1024
      END
    )                          AS max_size_mb,
    COUNT(CASE WHEN d.autoextensible = 'YES' THEN 1 END) AS autoextend_count
  FROM dba_data_files d
  GROUP BY d.tablespace_name
) sz
LEFT JOIN (
  SELECT tablespace_name, SUM(bytes/1024/1024) AS free_mb
  FROM dba_free_space
  GROUP BY tablespace_name
) f ON sz.tablespace_name = f.tablespace_name
JOIN dba_tablespaces t ON sz.tablespace_name = t.tablespace_name
ORDER BY pct_free_current ASC;

Step 3: Check Temporary Tablespace Usage

Temp tablespace usage requires querying different views.

-- Temporary tablespace usage (current active usage)
SELECT
  u.tablespace,
  ROUND(t.total_mb, 0)                AS total_mb,
  ROUND(u.used_mb, 0)                 AS used_mb,
  ROUND(t.total_mb - u.used_mb, 0)    AS free_mb,
  ROUND(u.used_mb / t.total_mb * 100, 1) AS used_pct
FROM (
  SELECT tablespace_name,
         SUM(bytes_used/1024/1024) AS used_mb,
         tablespace
  FROM v$temp_space_header
  GROUP BY tablespace_name, tablespace
) u
JOIN (
  SELECT tablespace_name,
         SUM(bytes/1024/1024) AS total_mb
  FROM dba_temp_files
  GROUP BY tablespace_name
) t ON u.tablespace = t.tablespace_name
ORDER BY used_pct DESC;

-- Who is consuming temp space right now?
SELECT s.username, s.sid, s.serial#,
       s.program, s.module,
       ROUND(u.blocks * 8192 / 1024 / 1024, 0) AS temp_mb,
       u.tablespace
FROM v$sort_usage u
JOIN v$session s ON u.session_addr = s.saddr
ORDER BY temp_mb DESC;

Step 4: Individual Datafile Space Breakdown

-- Detailed datafile usage — useful when a tablespace has multiple files
SELECT
  d.file_id,
  d.tablespace_name,
  SUBSTR(d.file_name, INSTR(d.file_name, '/', -1) + 1) AS filename,
  ROUND(d.bytes/1024/1024/1024, 2)   AS current_gb,
  ROUND(NVL(SUM(f.bytes),0)/1024/1024/1024, 2) AS free_gb,
  ROUND((1 - NVL(SUM(f.bytes),0)/d.bytes) * 100, 1) AS used_pct,
  d.autoextensible,
  ROUND(d.maxbytes/1024/1024/1024, 2) AS max_gb
FROM dba_data_files d
LEFT JOIN dba_free_space f
  ON d.file_id = f.file_id
GROUP BY d.file_id, d.tablespace_name, d.file_name,
         d.bytes, d.autoextensible, d.maxbytes
ORDER BY used_pct DESC;

Step 5: Track Tablespace Growth Trends Using AWR

AWR captures tablespace usage over time. Use this to project when a tablespace will fill up.

-- Tablespace growth trend from AWR (last 30 days)
SELECT
  TO_CHAR(sn.begin_interval_time, 'YYYY-MM-DD') AS snap_date,
  ts.tsname,
  ROUND(SUM(ts.tablespace_size * 8192) / 1024 / 1024 / 1024, 2) AS total_gb,
  ROUND(SUM(ts.tablespace_usedsize * 8192) / 1024 / 1024 / 1024, 2) AS used_gb,
  ROUND(SUM(ts.tablespace_usedsize) / SUM(ts.tablespace_size) * 100, 1) AS used_pct
FROM dba_hist_tbspc_space_usage ts
JOIN dba_hist_snapshot sn ON ts.snap_id = sn.snap_id
WHERE sn.begin_interval_time > SYSDATE - 30
GROUP BY TO_CHAR(sn.begin_interval_time, 'YYYY-MM-DD'), ts.tsname
ORDER BY ts.tsname, snap_date;

Step 6: Project Days Until Full

-- Calculate days until a tablespace reaches 95% based on recent growth
WITH daily_usage AS (
  SELECT
    tsname,
    TO_CHAR(sn.begin_interval_time, 'YYYY-MM-DD') AS snap_date,
    MAX(ts.tablespace_usedsize * 8192 / 1024 / 1024) AS used_mb,
    MAX(ts.tablespace_size * 8192 / 1024 / 1024)     AS total_mb
  FROM dba_hist_tbspc_space_usage ts
  JOIN dba_hist_snapshot sn ON ts.snap_id = sn.snap_id
  WHERE sn.begin_interval_time > SYSDATE - 14
  GROUP BY tsname, TO_CHAR(sn.begin_interval_time, 'YYYY-MM-DD')
),
growth_rate AS (
  SELECT
    tsname,
    REGR_SLOPE(used_mb, TO_NUMBER(TO_CHAR(TO_DATE(snap_date,'YYYY-MM-DD'),'J'))) AS mb_per_day,
    MAX(used_mb)  AS current_used_mb,
    MAX(total_mb) AS total_mb
  FROM daily_usage
  GROUP BY tsname
  HAVING REGR_SLOPE(used_mb, TO_NUMBER(TO_CHAR(TO_DATE(snap_date,'YYYY-MM-DD'),'J'))) > 0
)
SELECT
  tsname,
  ROUND(current_used_mb / 1024, 2) AS used_gb,
  ROUND(total_mb / 1024, 2)        AS total_gb,
  ROUND(mb_per_day, 0)             AS growth_mb_per_day,
  ROUND((total_mb * 0.95 - current_used_mb) / mb_per_day, 0) AS days_until_95pct
FROM growth_rate
WHERE (total_mb * 0.95 - current_used_mb) / mb_per_day < 60  -- Alert if < 60 days
ORDER BY days_until_95pct ASC;

Step 7: Set Up Automated Threshold Alerts

Oracle provides built-in tablespace threshold alerts through the Database Resource Manager. Configure them via DBMS_SERVER_ALERT.

-- Set warning threshold at 85% full, critical at 95%
BEGIN
  DBMS_SERVER_ALERT.SET_THRESHOLD(
    metrics_id       => DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL,
    warning_operator => DBMS_SERVER_ALERT.OPERATOR_GE,
    warning_value    => '85',
    critical_operator=> DBMS_SERVER_ALERT.OPERATOR_GE,
    critical_value   => '95',
    observation_period => 1,
    consecutive_occurrences => 1,
    instance_name    => NULL,
    object_type      => DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,
    object_name      => 'APP_DATA'
  );
END;
/

-- Check current alert thresholds
SELECT object_name, metrics_name, warning_value, critical_value, observation_period
FROM dba_thresholds
WHERE metrics_name = 'Tablespace Percent Used'
ORDER BY object_name;

-- View outstanding alerts
SELECT object_name, reason, suggested_action, time_suggested
FROM dba_outstanding_alerts
WHERE object_type = 'TABLESPACE'
ORDER BY time_suggested DESC;

Advanced: Full Monitoring Script

-- Comprehensive report: all tablespaces with traffic light status
SELECT
  tablespace_name,
  used_pct,
  CASE
    WHEN used_pct >= 95 THEN '** CRITICAL **'
    WHEN used_pct >= 85 THEN '* WARNING *'
    WHEN used_pct >= 75 THEN 'WATCH'
    ELSE 'OK'
  END AS alert_status,
  total_gb,
  free_gb,
  can_grow
FROM (
  SELECT
    t.tablespace_name,
    ROUND(current_gb, 2)   AS total_gb,
    ROUND(free_gb, 2)      AS free_gb,
    ROUND((1 - free_gb / NULLIF(current_gb, 0)) * 100, 1) AS used_pct,
    CASE WHEN autoext_cnt > 0
      THEN 'Yes (' || ROUND(max_gb, 1) || ' GB max)'
      ELSE 'No'
    END AS can_grow
  FROM (
    SELECT d.tablespace_name,
           SUM(d.bytes/1024/1024/1024) AS current_gb,
           SUM(CASE WHEN autoextensible = 'YES'
                 THEN maxbytes ELSE bytes
               END)/1024/1024/1024 AS max_gb,
           COUNT(CASE WHEN autoextensible='YES' THEN 1 END) AS autoext_cnt
    FROM dba_data_files d
    GROUP BY d.tablespace_name
  ) d
  LEFT JOIN (
    SELECT tablespace_name, SUM(bytes/1024/1024/1024) AS free_gb
    FROM dba_free_space GROUP BY tablespace_name
  ) f ON d.tablespace_name = f.tablespace_name
  JOIN dba_tablespaces t ON d.tablespace_name = t.tablespace_name
)
ORDER BY used_pct DESC;

Common Mistakes and Pitfalls

Monitoring only current size, ignoring MAXSIZE — A tablespace at 90% full may still have 100 GB of autoextend headroom. Report both current usage and maximum potential size.

Forgetting temp tablespace — dba_free_space does not include temporary tablespaces. Query v$temp_space_header and dba_temp_files separately.

Setting MAXSIZE UNLIMITED — Without a MAXSIZE cap, autoextend can fill the entire disk and take down the OS. Always cap with a sensible MAXSIZE.

Not monitoring SYSAUX — SYSAUX grows as AWR history accumulates, Streams queues grow, and other components consume space. Include SYSAUX in all monitoring queries.

Ignoring UNDO tablespace growth — Long-running transactions hold undo space. Monitor UNDO tablespace usage separately and correlate with UNDO_RETENTION settings.

Verification Queries

-- Quick sanity check: any tablespace > 90% full?
SELECT tablespace_name, used_pct
FROM (
  SELECT t.tablespace_name,
         ROUND((1 - SUM(NVL(f.bytes,0)) / SUM(d.bytes)) * 100, 1) AS used_pct
  FROM dba_tablespaces t
  JOIN dba_data_files d ON t.tablespace_name = d.tablespace_name
  LEFT JOIN dba_free_space f ON t.tablespace_name = f.tablespace_name
  GROUP BY t.tablespace_name
)
WHERE used_pct > 90
ORDER BY used_pct DESC;

Related Topics

• How to Add a Datafile - Expand a nearly-full tablespace
• How to Resize Datafiles - Grow or shrink datafiles
• How to Create a Tablespace - Create new tablespaces
• Administration Scripts - Full DBA toolkit