ORA-01653: unable to extend table in tablespace

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

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

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;

Do not assume a tablespace is bigfile. Oracle introduced bigfile defaults for SYSAUX, SYSTEM, and USER tablespaces in 26ai; a 23ai installation may still use smallfile defaults. Always check DBA_TABLESPACES.BIGFILE.

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)

For bigfile tablespaces, use DBMS_SPACE.SHRINK_TABLESPACE. It moves/reorganizes eligible objects and reports the resulting datafile size. Run analysis first, then shrink:

SET SERVEROUTPUT ON
BEGIN
  DBMS_SPACE.SHRINK_TABLESPACE(
    ts_name      => '&tablespace',
    shrink_mode  => DBMS_SPACE.TS_SHRINK_MODE_ANALYZE
  );
END;
/

BEGIN
  DBMS_SPACE.SHRINK_TABLESPACE('&tablespace');
END;
/

The official procedure signature is SHRINK_TABLESPACE(ts_name IN VARCHAR2, shrink_mode IN NUMBER, target_size IN NUMBER[, shrink_result OUT CLOB]). Exact smallfile-shrink availability and mode names are release-update specific; check the installed DBMS_SPACE package before using it on a smallfile 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.

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 ’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 slow commit cascade: when redo storage degrades and every transaction waits