ORA-01653 is the hard stop. A DML statement needs to extend a table segment, Oracle cannot allocate the next extent in the tablespace, and the statement fails immediately. There is no graceful degradation: the tablespace was performing normally a moment ago, and now any write that needs new space is rejected. Existing committed data is intact, but inserts, updates that grow row size, index maintenance behind constraints, and any operation that requires new extents all error out.

The error names the table and the tablespace. It does not tell you whether the wall you hit is the datafile MAXSIZE, the filesystem or ASM disk group, a smallfile per-file limit, or a fragmentation artefact from a previous failed allocation. The fix is usually fast (add a datafile, resize, or raise MAXSIZE), but the diagnosis determines which fix is correct, and the wrong one wastes the maintenance window.

The most common operator mistake with this error is computing utilization against the current allocated size of the datafile instead of the maximum size including autoextend. A tablespace can show “90% full” in a naive DBA_FREE_SPACE join and still have 200GB of headroom because AUTOEXTEND is ON with a high MAXSIZE. Equally, it can show “70% full” and be hours from ORA-01653 because the datafile is at MAXSIZE and the underlying filesystem has 5GB free. Read the right number.

What this means

When Oracle executes DML that grows a segment (insert, update that triggers row migration, parallel direct-path loads), it asks for a new extent. Extent allocation is a metadata operation against the tablespace. If the tablespace cannot honour the request, the call returns ORA-01653 for tables or ORA-01654 for indexes. The statement rolls back, but the failure mode is cliff-edge: normal until full, then hard error. See the “characteristic failure archetypes” section of How Oracle Database actually works in production for where this sits in the broader catalogue.

flowchart TD
  A[ORA-01653 on DML] --> B{Tablespace at MAXSIZE?}
  B -- No, headroom exists --> C[Filesystem or ASM disk group full]
  B -- Yes --> D{AUTOEXTEND on datafile?}
  D -- OFF --> E[Enable AUTOEXTEND
or add datafile]
  D -- ON, at MAXSIZE --> F[Raise MAXSIZE
or add datafile]
  D -- ON, OS storage full --> G[Add storage
or reclaim space]
  C --> G

The diagnostic tree above is what the rest of this article walks through. The single most important branch is the first one: if the tablespace is not at MAXSIZE, the failure is at the storage layer, not inside Oracle.

Common causes

CauseWhat it looks likeFirst thing to check
Tablespace at MAXSIZE with AUTOEXTEND OFFDBA_DATA_FILES.AUTOEXTENSIBLE = NO, file is at current sizeSELECT autoextensible FROM dba_data_files WHERE tablespace_name = '...'
AUTOEXTEND ON but MAXSIZE reachedAUTOEXTENSIBLE = YES, BYTES = MAXBYTESCompare BYTES to MAXBYTES per datafile
AUTOEXTEND ON, MAXSIZE not reached, filesystem/ASM fullBYTES < MAXBYTES but OS df shows volume fulldf -h on the datafile mount, or asmcmd lsdg
Failed bulk insert left extents allocatedTable is logically empty but DBA_SEGMENTS.BYTES is largeSELECT bytes FROM dba_segments WHERE segment_name = '...'
SYSTEM/SYSAUX growth from audit trail or AWRSYSTEM or SYSAUX full, no obvious application causeCheck SYS.AUD$, FGA_LOG$, AWR retention
Smallfile per-file limit (8K block, approx 32GB)Single datafile cannot grow further, tablespace is smallfileCheck DBA_TABLESPACES.BIGFILE and per-file MAXBYTES

Quick checks

These are read-only and safe to run during the incident.

-- Authoritative utilization against MAXSIZE (11gR2+)
SELECT tablespace_name, ROUND(used_percent, 2) AS used_pct
FROM dba_tablespace_usage_metrics
ORDER BY used_percent DESC;

-- Per-datafile detail with autoextend awareness
SELECT file_id, file_name,
       bytes/1048576 AS current_mb,
       maxbytes/1048576 AS max_mb,
       autoextensible,
       increment_by
FROM dba_data_files
WHERE tablespace_name = '&tablespace';

-- Largest segments in the affected tablespace
SELECT owner, segment_name, segment_type, bytes/1048576 AS mb
FROM dba_segments
WHERE tablespace_name = '&tablespace'
ORDER BY bytes DESC
FETCH FIRST 20 ROWS ONLY;

-- Free space inside the current allocation (useful when AUTOEXTENSIBLE = NO)
SELECT tablespace_name, COUNT(*) AS free_extents,
       SUM(bytes)/1048576 AS total_free_mb,
       MAX(bytes)/1048576 AS largest_free_mb
FROM dba_free_space
WHERE tablespace_name = '&tablespace'
GROUP BY tablespace_name;

# Filesystem space on the datafile directory
df -h /u01/oradata/
# ASM disk group utilization
asmcmd lsdg

-- Is the named table actually full of data, or empty-but-allocated?
SELECT segment_name, bytes/1048576 AS mb, extents
FROM dba_segments
WHERE segment_name = '&table_name';

How to diagnose it

  1. Read the ORA-01653 message text. It names the table and the tablespace. The tablespace is the unit of failure, not the table. The table is just the segment that happened to need the next extent.
  2. Run the DBA_TABLESPACE_USAGE_METRICS query. USED_PERCENT there is computed against maximum capacity, so this is the authoritative number. Anything above 95% with no autoextend or filesystem headroom is the immediate cause.
  3. If USED_PERCENT is below 95%, you are hitting a different limit. Cross-check with the per-datafile query. The four common cases are: AUTOEXTENSIBLE = NO and the file is full; AUTOEXTENSIBLE = YES and BYTES = MAXBYTES; AUTOEXTENSIBLE = YES, BYTES < MAXBYTES, but the filesystem or ASM disk group has no room; or the table itself holds allocated extents from a previous failed transaction.
  4. Confirm which case you are in by comparing BYTES, MAXBYTES, AUTOEXTENSIBLE, and the OS-level df output for the datafile path. Do not skip the OS check: a tablespace with autoextend headroom is still blocked if the volume is full.
  5. If none of those explain it, check the segment. A failed INSERT that errors with ORA-01653 still allocates the extent that triggered the failure; statement rollback recovers the data, not the extent. A table can show zero rows and consume gigabytes. This is well-documented Oracle behaviour and it catches teams off guard because the table looks empty.
  6. If the tablespace is SYSTEM or SYSAUX, suspect audit trail growth (SYS.AUD$, FGA_LOG$) or AWR retention. These grow without any application write activity, and the symptom looks identical to a user tablespace full event.

Metrics and signals to monitor

SignalWhy it mattersWarning sign
DBA_TABLESPACE_USAGE_METRICS.USED_PERCENTComputed against MAXSIZE, not current sizeGreater than 85% TICKET, greater than 95% PAGE
Per-tablespace growth rate (delta on BYTES)Linear projection gives days to fullGrowth rate doubling versus 30-day baseline
DBA_DATA_FILES.AUTOEXTENSIBLE and MAXBYTESDetermines whether USED_PERCENT reflects real headroomAUTOEXTENSIBLE = NO with no add-file plan
Filesystem or ASM disk group free spaceTablespace headroom means nothing if storage is fullLess than 20% free on the datafile LUN
DBA_SEGMENTS.BYTES for empty tablesCatches the failed-insert extent leakMB far larger than row count would suggest
Alert log ORA-01653 and ORA-01654The hard signal that an allocation already failedAny occurrence in business hours

Fixes

Pick the fix that matches the cause from the diagnostic section.

Add a datafile (smallfile tablespace)

The action Oracle’s own error documentation recommends. Fast, online, does not affect existing data.

-- Add a datafile with a bounded MAXSIZE.
ALTER TABLESPACE &tablespace
ADD DATAFILE '/u01/oradata/&db/&tablespace_02.dbf'
SIZE 1G
AUTOEXTEND ON NEXT 100M
MAXSIZE 32G;

Tradeoff: smallfile datafiles on an 8K block database max out at approximately 32GB. If your tablespace needs to grow beyond a few hundred GB, you will be adding many files. Plan the layout and naming.

Resize the existing datafile

Faster than adding a file when the underlying storage has room. Works for both smallfile and bigfile.

-- Resize a smallfile datafile
ALTER DATABASE DATAFILE '/u01/oradata/&db/&tablespace_01.dbf'
RESIZE 50G;

For bigfile tablespaces, you can resize through the tablespace itself:

-- Bigfile tablespace resize
ALTER TABLESPACE &tablespace RESIZE 50G;

Tradeoff: requires free space at the OS or ASM level. If the volume is full, resize fails.

Enable or raise autoextend

If AUTOEXTENSIBLE is OFF or MAXSIZE is too low, fix the configuration, not just the immediate shortage.

-- Enable autoextend with an explicit MAXSIZE
ALTER DATABASE DATAFILE '/u01/oradata/&db/&tablespace_01.dbf'
AUTOEXTEND ON NEXT 100M MAXSIZE 50G;

Tradeoff: AUTOEXTEND ON MAXSIZE UNLIMITED is risky. A runaway insert can fill an entire mount point and crash other databases or the OS. Set an explicit bounded MAXSIZE per datafile.

Reclaim space from a failed-insert extent leak

When an INSERT failed with ORA-01653 and the extents stayed allocated, the table is logically empty but physically large. Return the unused extents:

-- Returns extents above the high water mark to the tablespace
ALTER TABLE &owner.&table_name DEALLOCATE UNUSED;

For a table that is truly empty and can be cleared, TRUNCATE is faster and reclaims everything:

-- Destructive: removes all rows. Confirm with the application owner.
TRUNCATE TABLE &owner.&table_name;

Shrink objects and the tablespace (23ai/26ai)

Oracle 23ai added DBMS_SPACE.SHRINK_TABLESPACE for bigfile tablespaces. It reorganizes objects online and resizes the datafile down. Useful when the tablespace is full of fragmented or reclaimable space but you cannot add storage.

-- Analyze first, then shrink.
EXEC DBMS_SPACE.SHRINK_TABLESPACE('&tablespace');

Move audit tables out of SYSTEM

If SYS.AUD$ or FGA_LOG$ is consuming SYSTEM, migrate them to a dedicated tablespace. This is documented behaviour for Amazon RDS for Oracle as well as standard Oracle.

BEGIN
  DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,
    audit_trail_location_value => '&audit_tablespace');
END;
/

Prevention

  • Monitor USED_PERCENT from DBA_TABLESPACE_USAGE_METRICS, not DBA_FREE_SPACE alone. The view accounts for autoextend. Raw DBA_FREE_SPACE shows free space inside the current allocation, hiding MAXSIZE headroom.
  • Set explicit MAXSIZE on every autoextensible datafile. UNLIMITED is how a single runaway insert fills a volume shared with other databases.
  • Track growth rate per tablespace. Compute the 30-day delta on segment bytes. Linear projection gives days-to-full. Growth that doubles after an application change is a leading indicator.
  • Watch filesystem and ASM disk group free space separately. Tablespace headroom is meaningless if the storage layer is full.
  • For bigfile tablespaces in 23ai+, plan a shrink job for tablespaces prone to churn (LOB segments, audit tables, ETL staging). DBMS_SPACE.SHRINK_TABLESPACE is the supported reclaim path.
  • For audit tables in SYSTEM, migrate them to a dedicated tablespace before growth forces the issue during a peak window.
  • Watch for the failed-insert extent pattern in batch jobs. A nightly ETL that errors on ORA-01653 leaves extents behind. Add a post-job check on DBA_SEGMENTS.BYTES for the load target.

How Netdata helps

  • Per-second tablespace utilization from DBA_TABLESPACE_USAGE_METRICS, with USED_PERCENT computed against MAXSIZE, surfaces real headroom rather than just current allocation. The cliff-edge nature of ORA-01653 makes per-second granularity useful for catching the moment growth accelerates.
  • Growth-rate trending on a per-tablespace basis converts raw utilization into days-to-full projections, which is the number that lets you plan a datafile add before the incident.
  • Correlation with filesystem and disk metrics at the host level lets you distinguish “tablespace at MAXSIZE” from “filesystem full underneath a tablespace that thinks it has headroom”. The two fixes are different.
  • Alert log parsing for ORA-01653 and ORA-01654 turns the error itself into a paging signal, so the failed allocation reaches you directly rather than as an application ticket.
  • Anomaly detection on segment growth rates flags days when a table is growing several times faster than its baseline, which is the leading indicator that an ORA-01653 is coming.

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