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

CauseWhat it looks likeFirst thing to check
Data growth exceeded capacityUsed percent rising steadily over weeks; one or two tablespaces hit 95%+DBA_TABLESPACE_USAGE_METRICS.USED_PERCENT trend
Autoextend hit MAXSIZEUsed percent against MAX is high; current size equals MAXSIZEDBA_DATA_FILES.MAXBYTES vs BYTES
Underlying filesystem or ASM fullAutoextend is ON but datafile is not growing; OS disk usage near 100%df -h on the mount point, or asmcmd lsdg
UNDO tablespace fullORA-30036 alongside ORA-01653; large uncommitted transactionV$UNDOSTAT.NOSPACEERRCNT, V$TRANSACTION.USED_UBLK
SYSAUX growth from AWRSYSAUX full; AWR or optimizer stats history consuming spaceV$SYSAUX_OCCUPANTS
Runaway batch or bulk loadSudden spike in used percent over minutes; one segment growing fastDBA_SEGMENTS ordered by bytes, recent V$SESSION_LONGOPS

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

SignalWhy it mattersWarning sign
DBA_TABLESPACE_USAGE_METRICS.USED_PERCENTAuthoritative used-against-MAX metric; accounts for autoextendSustained above 85%
DBA_DATA_FILES.MAXBYTES minus BYTESHeadroom the datafile can still grow on its ownApproaching 0 for any single file
Filesystem free on the datafile mountWithout OS headroom, autoextend cannot grow the fileBelow 20%
ASM disk group free space (if ASM)Equivalent of filesystem free for ASM-backed filesBelow 20%
V$UNDOSTAT.NOSPACEERRCNTActive undo exhaustion; ORA-30036 countAny non-zero value
V$UNDOSTAT.SSOLDERRCNTRead-consistency failure; ORA-01555 countAny non-zero value
V$SYSAUX_OCCUPANTS spaceAWR, optimizer stats history, Streams queuesGrowth concentrated in one occupant
DBA_TEMP_FREE_SPACE allocated vs totalTemp tablespace pressure (invisible to DBA_FREE_SPACE)Above 80%
Alert log ORA-01652Temp tablespace fullAny occurrence during peak load

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 bigfile UNDO tablespaces in 23ai/26ai, DBMS_SPACE.SHRINK_TABLESPACE with TS_MODE_SHRINK can reclaim space online after a large transaction commits. This procedure is not available in 19c.

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 TABLESPACE_PCT_FULL so the database itself pages before the cliff. OEM maintains its own metric thresholds separately from server-side alerts; 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.