Oracle tablespace full: monitoring used percent against max capacity

A tablespace full event in Oracle is a cliff-edge failure. Operations that worked seconds ago return ORA-01653 (table) or ORA-01654 (index). The instance is OPEN, the listener responds, existing SELECTs may still succeed, but any INSERT, UPDATE, or index maintenance that needs to allocate a new extent fails. There is no graceful degradation.

Oracle allocates space on demand, extent by extent. The moment a segment cannot extend, the operation errors out. If the failing tablespace is UNDO, the failure cascades to every transaction in the database. If it is SYSTEM or SYSAUX, internal operations can stall.

The most common operator mistake is monitoring against current allocation rather than against maximum capacity. A tablespace that is 99% full against its current datafile size but has autoextend enabled with headroom is not an emergency. The same tablespace at 99% of MAXSIZE, with the underlying filesystem also nearly full, is hours from a hard outage. This article is about making that distinction visible.

What this means

When Oracle reports ORA-01653 (“unable to extend table … in tablespace …”) or ORA-01654 (“unable to extend index …”), a segment tried to allocate a new extent and the tablespace could not provide one. Three distinct conditions produce the same error from the application’s perspective.

1. Logical exhaustion. The datafile or datafiles are at their maximum size (MAXSIZE for autoextensible files, current size for non-autoextensible files), or the tablespace has hit its quota. The fix is to add or resize datafiles.
2. Physical exhaustion. Autoextend is enabled but the underlying filesystem or ASM disk group has no free space, so the datafile cannot grow. The fix is to add storage at the OS or ASM layer.
3. Segment-level fragmentation. The tablespace as a whole has room, but the specific segment hitting the error cannot get a contiguous extent because free space is fragmented or held by other segments. Rarer with locally managed tablespaces and ASSM, but still happens with very large objects.

Distinguishing these three is the entire job of diagnosing a tablespace full event. The right metric answers all three at once: used percent against MAX capacity, cross-referenced with filesystem free space.

Common causes

Quick checks

Read-only and safe to run during an incident.

-- Current tablespace utilization against MAX capacity (the correct view)
SELECT TABLESPACE_NAME, ROUND(USED_PERCENT, 2) AS used_pct
FROM DBA_TABLESPACE_USAGE_METRICS
ORDER BY USED_PERCENT DESC;

-- Detail: current size vs MAX size vs free, per tablespace
SELECT df.TABLESPACE_NAME,
       ROUND(df.TOTAL_MB, 2) AS current_mb,
       ROUND(df.MAX_MB, 2) AS max_mb,
       ROUND(NVL(fs.FREE_MB, 0), 2) AS free_mb,
       ROUND((df.TOTAL_MB - NVL(fs.FREE_MB, 0)) / df.MAX_MB * 100, 2) AS used_pct_of_max
FROM (SELECT TABLESPACE_NAME, SUM(BYTES)/1048576 AS TOTAL_MB,
             SUM(DECODE(AUTOEXTENSIBLE,'YES',MAXBYTES,BYTES))/1048576 AS MAX_MB
      FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) df
LEFT JOIN (SELECT TABLESPACE_NAME, SUM(BYTES)/1048576 AS FREE_MB
           FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) fs
ON df.TABLESPACE_NAME = fs.TABLESPACE_NAME
ORDER BY used_pct_of_max DESC;

-- Confirm the ORA-01653/01654 in the alert log
SELECT originating_timestamp, message_text
FROM V$DIAG_ALERT_EXT
WHERE message_text LIKE '%ORA-01653%' OR message_text LIKE '%ORA-01654%'
ORDER BY originating_timestamp DESC
FETCH FIRST 20 ROWS ONLY;

-- Autoextend state and MAXSIZE per datafile (filter to the tablespaces at risk)
SELECT TABLESPACE_NAME, FILE_NAME, BYTES/1048576 AS cur_mb,
       MAXBYTES/1048576 AS max_mb, AUTOEXTENSIBLE, INCREMENT_BY
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME IN (
  SELECT TABLESPACE_NAME FROM DBA_TABLESPACE_USAGE_METRICS
  WHERE USED_PERCENT > 85
)
ORDER BY TABLESPACE_NAME, FILE_NAME;

# Find the datafile path first, then check its mount
df -h /u01/oradata/MYDB/users01.dbf
# For ASM-backed files:
# asmcmd lsdg

-- Temp tablespace: DBA_FREE_SPACE excludes temp, so use this view
SELECT TABLESPACE_NAME,
       TABLESPACE_SIZE/1048576 AS total_mb,
       ALLOCATED_SPACE/1048576 AS allocated_mb,
       FREE_SPACE/1048576 AS free_mb
FROM DBA_TEMP_FREE_SPACE;

-- UNDO breakdown (active, unexpired, expired)
SELECT STATUS, SUM(BYTES)/1048576 AS mb
FROM DBA_UNDO_EXTENTS
GROUP BY STATUS;
-- ACTIVE   = needed for current transactions (cannot reclaim)
-- UNEXPIRED = retained for UNDO_RETENTION but reclaimable under pressure
-- EXPIRED  = free for reuse

How to diagnose it

flowchart TD
  A[ORA-01653 or ORA-01654] --> B[USED_PERCENT from DBA_TABLESPACE_USAGE_METRICS]
  B --> C{Above 95% of MAX?}
  C -- No --> D[Check underlying FS or ASM free]
  C -- Yes --> E[Autoextend enabled?]
  E -- Yes --> F{Current size = MAXSIZE?}
  E -- No --> G[Add or resize datafile]
  F -- Yes --> G
  F -- No --> H[Add OS or ASM storage]
  D -- Low --> H
  D -- OK --> I[Check segment fragmentation]

1. Confirm the failing tablespace. The ORA message names the tablespace. Cross-check with the highest-USED_PERCENT row from DBA_TABLESPACE_USAGE_METRICS. They should agree.
2. Compare USED_PERCENT to MAX, not to current allocation. DBA_TABLESPACE_USAGE_METRICS.USED_PERCENT already does this for you. A hand-rolled query against DBA_FREE_SPACE and DBA_DATA_FILES.BYTES measures the wrong denominator and misses autoextend headroom. That is the single most common diagnostic error in this domain.
3. Determine whether the constraint is MAXSIZE or the storage layer. If the datafile’s current BYTES equals its MAXBYTES, the tablespace cannot grow even if the filesystem has space. If BYTES is below MAXBYTES but USED_PERCENT is still climbing, the filesystem or ASM disk group is the constraint.
4. Identify the consuming segment. V$SESSION.SQL_ID at the time of the error points at the statement. DBA_SEGMENTS ordered by BYTES shows the largest objects. For runaway growth, check V$SESSION_LONGOPS and the alert log for bulk load or parallel DML activity.
5. Handle the special tablespaces differently. UNDO, SYSAUX, SYSTEM, and temp each have their own failure modes and remediation paths. See below.

Metrics and signals to monitor

Fixes

Add or resize a datafile

For a smallfile tablespace, add a datafile or resize an existing one. For a bigfile tablespace, resize the single datafile up to its maximum.

-- Add a datafile to a smallfile tablespace
ALTER TABLESPACE users ADD DATAFILE '/u01/oradata/MYDB/users02.dbf' SIZE 10G
  AUTOEXTEND ON NEXT 1G MAXSIZE 50G;

-- Resize an existing datafile (smallfile or bigfile)
ALTER DATABASE DATAFILE '/u01/oradata/MYDB/users01.dbf' RESIZE 50G;

-- Raise MAXSIZE on an autoextensible file
ALTER DATABASE DATAFILE '/u01/oradata/MYDB/users01.dbf'
  AUTOEXTEND ON MAXSIZE 100G;

Avoid MAXSIZE UNLIMITED on any datafile that shares its filesystem with redo logs, archive logs, or the OS. A runaway segment can fill the entire mount point and crash the database or the host. Set a bounded MAXSIZE that leaves room for other consumers.

Add storage at the filesystem or ASM layer

If autoextend is enabled but the datafile is not growing, the underlying storage is the constraint. Add space at the OS layer (extend the LUN, grow the filesystem) or at the ASM layer (add disks to the disk group, rebalance).

-- ASM: add disks to the disk group and rebalance
-- ALTER DISKGROUP DATA ADD DISK '<path>' REBALANCE POWER 5;

Enable autoextend if it is off

Many systems ship datafiles with AUTOEXTENSIBLE = 'NO'. This is safe but creates silent cliffs. If the storage layer has headroom and you monitor USED_PERCENT against MAX, enabling autoextend with a bounded MAXSIZE gives a soft warning window before the hard error.

ALTER DATABASE DATAFILE '/u01/oradata/MYDB/users01.dbf'
  AUTOEXTEND ON NEXT 500M MAXSIZE 50G;

Reclaim space from SYSAUX

SYSAUX growth is usually from AWR retention, optimizer statistics history, or Streams/GoldenGate queues. Identify the largest occupants and purge or shrink them.

-- What is consuming SYSAUX
SELECT OCCUPANT_NAME, SPACE_USAGE_KBYTES/1024 AS mb
FROM V$SYSAUX_OCCUPANTS
ORDER BY SPACE_USAGE_KBYTES DESC;

-- Modify AWR retention (adjust to your diagnostic needs; do not purge blindly)
-- EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention => 8*24*60);

Do not blindly drop AWR snapshots during an incident. AWR is your diagnostic record. Shrink retention only as a planned change, and confirm you have an external metrics store if you do.

Resolve UNDO pressure

If the tablespace is UNDO and ORA-30036 is occurring alongside ORA-01653, the issue is active undo exhaustion, usually from a single large uncommitted transaction. Find the largest undo consumer first.

SELECT s.SID, s.SERIAL#, s.USERNAME, s.SQL_ID,
       t.USED_UBLK
FROM V$TRANSACTION t JOIN V$SESSION s ON t.SES_ADDR = s.SADDR
ORDER BY t.USED_UBLK DESC;

If the transaction can be killed, kill it. If it cannot, add space to the UNDO tablespace. Increasing UNDO_RETENTION without adding space makes the problem worse, not better.

For tablespaces supported by Oracle 23ai and later, DBMS_SPACE.SHRINK_TABLESPACE can reorganize objects online and resize the tablespace after a large transaction commits. The documented procedure takes a shrink_mode argument; TS_MODE_SHRINK was the older RU 23.6 name, while RU 23.7 and later document TS_SHRINK_MODE_ONLINE for the online default (TS_SHRINK_MODE_ANALYZE for analysis, TS_SHRINK_MODE_AUTO for online-then-offline, and TS_SHRINK_MODE_OFFLINE for offline). This procedure is not available in 19c. Test it on a copy first; unsupported or unmovable objects can prevent the desired result.

Prevention

Set thresholds against MAX capacity, not current allocation:

• PLAN at 75% of MAX. Trend, project runway, plan capacity additions.
• TICKET at 85% of MAX. Someone needs to investigate before the weekend.
• PAGE at 95% of MAX with no autoextend or storage headroom. This is hours from a hard error.

Track growth rate: days_remaining = free_space_against_max / daily_growth_rate. A tablespace at 80% of MAX growing 1% per day has roughly 20 days of runway. One at 90% growing 5% per day has two.

Include the special tablespaces in the same alerting policy:

• SYSTEM is usually stable but should be trended. Sudden growth is almost always a metadata event (audit trail, new schema objects, new database features).
• SYSAUX needs active management of AWR retention and statistics history. It is the most common source of “why did this fill up” surprises.
• UNDO must be measured by undo segment state (ACTIVE, UNEXPIRED, EXPIRED), not by USED_PERCENT alone. USED_PERCENT in DBA_TABLESPACE_USAGE_METRICS includes both expired and unexpired undo, which can be misleading for capacity planning.
• Temp needs its own view (DBA_TEMP_FREE_SPACE or V$TEMP_SPACE_HEADER). It is invisible to DBA_FREE_SPACE.

Set up DBMS_SERVER_ALERT.SET_THRESHOLD for DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL with OBJECT_TYPE_TABLESPACE so the database itself records threshold violations in DBA_THRESHOLDS; the Oracle administrator guide documents this server-side alert mechanism. Enterprise Manager may maintain its own metric thresholds separately, so changes made in OEM do not necessarily propagate to DBA_THRESHOLDS. Confirm where your alerting actually sources its thresholds before relying on either path.

Verify autoextend MAXSIZE is bounded on every datafile. AUTOEXTEND ON MAXSIZE UNLIMITED is an outage waiting to happen: a runaway segment can consume the entire filesystem and crash the OS or other databases sharing the storage.

Validate backups before you need them. A tablespace full event is a fine time to discover that RMAN has been failing for a week and the datafile you are about to resize is also not recoverable. Check V$RMAN_BACKUP_JOB_DETAILS as part of incident triage.

How Netdata helps

• Per-second collection of tablespace utilization against MAX capacity (not just current allocation) makes the autoextend headroom cliff visible as a trend, not a surprise. The denominator is what makes the signal useful.
• Correlating USED_PERCENT with OS-level filesystem and ASM disk group metrics distinguishes MAXSIZE exhaustion (logical) from underlying storage exhaustion (physical) in a single pane. That is the most common diagnostic fork in a tablespace full incident.
• ML anomaly detection on growth rate catches the runaway-batch and bulk-load pattern minutes before the hard error, which is the window where adding a datafile or killing the session is still cheap.
• Undo signals (NOSPACEERRCNT, SSOLDERRCNT, ACTIVE vs EXPIRED extents) are surfaced alongside tablespace utilization, so an ORA-30036 cascade does not get misdiagnosed as ordinary data growth.
• Alerting at 75/85/95% of MAX with per-second resolution avoids both false positives from brief spikes and false negatives from slow drifts that aggregate monitoring misses.
• SYSAUX occupants and AWR retention are tracked as part of the database signal set, so silent SYSAUX growth does not blind the diagnostics layer exactly when you need it.

Netdata’s Oracle Database monitoring with Netdata brings these signals together with per-second metrics and ML anomaly detection.

Related guides

• How Oracle Database actually works in production: a mental model for operators
• Oracle archive log destination full: V$ARCHIVE_DEST_STATUS, the ERROR state, and space
• Oracle ‘Thread N cannot allocate new log’: the archive hang that masquerades as up
• Oracle ‘Checkpoint not complete’: redo log sizing, DBWn, and log-switch stalls
• Oracle Fast Recovery Area full: db_recovery_file_dest_size, reclaimable space, and DELETE OBSOLETE
• Oracle ’log file sync’ waits: slow commits, LGWR, and the redo path
• Oracle Database monitoring checklist: the signals every production instance needs
• Oracle Database monitoring maturity model: from survival to expert
• ORA-00257: archiver error, connect internal only until freed
• Oracle redo generation rate: capacity planning for archiving and Data Guard
• Oracle redo log switch frequency: undersized logs and checkpoint pressure
• Oracle RMAN backup failures: V$RMAN_BACKUP_JOB_DETAILS and silent RPO loss