ORA-01654: unable to extend index in tablespace

When ORA-01654 fires, an index segment tried to allocate a new extent and could not. The statement fails. Depending on the index, this can stop a bulk load, an ALTER INDEX ... REBUILD, or any DML that modifies the index. It is the index-specific sibling of ORA-01653: same mechanism (segment extent allocation failure), same fix surface (tablespace capacity), different failing object.

Two operator surprises are common. First, the failing index often lives in a different tablespace than its base table. Default schemas create indexes alongside tables, but production layouts commonly split indexes into a dedicated INDX tablespace. When the error fires, investigate the index’s tablespace, not the table’s.

Second, “enough free space” can still produce ORA-01654. If the tablespace is locally managed with UNIFORM extents, or AUTOALLOCATE is in a tier that asks for a large contiguous extent, Oracle may fail to allocate even when DBA_FREE_SPACE shows free bytes. The relevant question is whether a single contiguous free extent is large enough, not whether there is free space in aggregate.

What this means

The error text includes the index name, the number of blocks Oracle tried to allocate, and the tablespace name:

ORA-01654: unable to extend index <schema>.<index> by <N> in tablespace <name>

The “by N” value is in blocks, not bytes. With an 8K block size, “by 8192” means Oracle asked for approximately 64 MB of contiguous space.

flowchart TD
  A[ORA-01654 fires] --> B{Index tablespace actually full?}
  B -- Yes, raw space gone --> C[Add space: datafile, resize, autoextend]
  B -- Free space present --> D{Extent policy?}
  D -- AUTOALLOCATE --> E[Tiered extent larger than any single free extent]
  D -- UNIFORM --> F[Free extents fragmented, none >= NEXT size]
  E --> G[Add datafile or reclaim from failed loads]
  F --> G
  B -- Autoextend at MAXSIZE --> H[Resize datafile or add file]

The error is a hard stop for the failing statement, not for the instance. Sessions touching unrelated segments continue. However, if the failing index backs a primary key or unique constraint on a high-volume insert path, every insert on that table can fail in turn.

Common causes

Quick checks

-- Confirm the failing index and its tablespace
SELECT OWNER, INDEX_NAME, TABLESPACE_NAME, STATUS
FROM DBA_INDEXES
WHERE INDEX_NAME = '<index_name>' AND OWNER = '<schema>';

-- Tablespace utilization including autoextend (11gR2+)
SELECT TABLESPACE_NAME, ROUND(USED_PERCENT, 2) AS used_pct
FROM DBA_TABLESPACE_USAGE_METRICS
WHERE TABLESPACE_NAME = '<tablespace_name>';

-- Free space, max capacity, autoextend headroom, largest free extent
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(NVL(fs.MAX_FREE_EXTENT_MB, 0), 2) AS largest_free_extent_mb
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,
                  MAX(BYTES)/1048576 AS MAX_FREE_EXTENT_MB
           FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) fs
ON df.TABLESPACE_NAME = fs.TABLESPACE_NAME
WHERE df.TABLESPACE_NAME = '<tablespace_name>';

-- Largest single contiguous free extents (fragmentation check)
SELECT TABLESPACE_NAME, FILE_ID, BLOCK_ID,
       BYTES/1048576 AS free_extent_mb
FROM DBA_FREE_SPACE
WHERE TABLESPACE_NAME = '<tablespace_name>'
ORDER BY BYTES DESC
FETCH FIRST 10 ROWS ONLY;

-- Segment size of the failing index
SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE,
       BYTES/1048576 AS mb, TABLESPACE_NAME
FROM DBA_SEGMENTS
WHERE SEGMENT_NAME = '<index_name>' AND OWNER = '<schema>';

-- Tablespace extent management and allocation type
SELECT TABLESPACE_NAME, EXTENT_MANAGEMENT, ALLOCATION_TYPE,
       SEGMENT_SPACE_MANAGEMENT, BIGFILE
FROM DBA_TABLESPACES
WHERE TABLESPACE_NAME = '<tablespace_name>';

# Underlying filesystem or ASM headroom
df -h <mount_point_of_datafiles>
# For ASM disk groups: asmcmd lsdg

How to diagnose it

1. Confirm which tablespace the failing index lives in. Use DBA_INDEXES.TABLESPACE_NAME, not the table’s tablespace. Splitting tables and indexes across tablespaces is common.

2. Compare requested extent size to actual free space. Multiply the “by N” value from the error by the block size (typically 8192 bytes) to get the requested extent size. Compare that against the largest single contiguous free extent in DBA_FREE_SPACE, not the sum of all free bytes. If sum-of-free is large but largest-single-extent is smaller than the request, you have fragmentation, not capacity exhaustion.

3. Check whether autoextend is enabled and whether it has hit its cap. AUTOEXTENSIBLE = YES is necessary but not sufficient. BYTES vs MAXBYTES shows whether autoextend still has room. If BYTES = MAXBYTES, autoextend cannot help.

4. Check the tablespace allocation policy. ALLOCATION_TYPE = SYSTEM means AUTOALLOCATE: Oracle chooses extent sizes from a tiered schedule (64K, 1M, 8M, 64M, …), so extent requests can step up sharply as a segment grows. ALLOCATION_TYPE = UNIFORM means every extent is a fixed size. AUTOALLOCATE can surprise operators because a request for a large extent can fail when only smaller free extents exist.

5. Verify whether a failed direct-path load left extents behind. A failed INSERT /*+ APPEND */, SQL*Loader direct path, or CREATE INDEX that errored on ORA-01654 may leave the segment at its post-failure size. The space stays allocated even though the rows are not there.

6. Rule out the underlying storage layer. If DBA_DATA_FILES.MAXBYTES allows growth but the filesystem or ASM disk group is full, autoextend attempts will fail. The alert log records the underlying OS error alongside the ORA-01654.

7. For legacy dictionary-managed tablespaces only, check DBA_INDEXES.NEXT_EXTENT and DBA_INDEXES.MAX_EXTENTS. For locally managed tablespaces (the default since 9i), the allocation policy drives the actual extent size and these columns are informational.

Metrics and signals to monitor

Fixes

Add space to the tablespace

The fastest relief. The choice depends on tablespace type.

For a smallfile tablespace, add a datafile:

-- Adds a new datafile to a smallfile tablespace
ALTER TABLESPACE <tablespace_name>
  ADD DATAFILE '/u01/oradata/<db_unique_name>/<tablespace>_02.dbf'
  SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 30G;

Smallfile datafiles cap at 2^22 blocks. With 8K blocks that is ~32 GB; with 32K blocks, ~128 GB. Check DB_BLOCK_SIZE before setting MAXSIZE.

Always set an explicit MAXSIZE. MAXSIZE UNLIMITED combined with AUTOEXTEND ON can silently fill the entire mount point, which converts an index-segment problem into a database-wide outage when other tablespaces on the same volume cannot extend.

For a bigfile tablespace, resize the single datafile instead:

-- Resizes a bigfile tablespace's single datafile
ALTER TABLESPACE <tablespace_name> RESIZE 50G;

Bigfile tablespaces have a single datafile that can be much larger than smallfile’s per-file ceiling, so the resize path is preferred over adding a file (which is not supported for bigfile).

Enable or fix autoextend

If autoextend is off, enabling it provides automatic headroom:

ALTER DATABASE DATAFILE '<file_path>'
  AUTOEXTEND ON NEXT 100M MAXSIZE 30G;

If autoextend is on but capped at MAXSIZE, raise the cap or add a new file.

Address fragmentation with free space present

When DBA_FREE_SPACE shows ample total free space but no single extent satisfies the request, adding a new datafile is the most reliable fix. This is especially true for UNIFORM locally managed tablespaces, where every extent is the same size and Oracle cannot satisfy a request with a smaller free chunk.

Locally managed tablespaces do not coalesce free space the way dictionary-managed tablespaces did. Do not expect SMON to defragment a fragmented LMT; add a datafile instead.

Reclaim space from failed direct-path loads

A failed direct-path INSERT or index build may have left extents allocated above the data actually loaded. Reclaim them:

ALTER INDEX <schema>.<index> DEALLOCATE UNUSED;

This returns unused space above the high-water mark to the tablespace free pool. It is safe to run online. It does not shrink below the high-water mark.

Rebuild the index into a tablespace with space

If the current index tablespace is structurally constrained and another tablespace has capacity, a rebuild moves the segment:

-- Rebuilds the index into a different tablespace
ALTER INDEX <schema>.<index> REBUILD TABLESPACE <new_tablespace>;

The rebuild needs space in the target tablespace approximately equal to the existing index size plus working room. A failed rebuild with ORA-01654 in the target tablespace is a common recursive trigger for this error.

Shrink the index segment

For indexes with significant empty space below the high-water mark (typical after large DELETEs on monotonically increasing keys), COALESCE or SHRINK SPACE can consolidate:

ALTER INDEX <schema>.<index> COALESCE;
ALTER INDEX <schema>.<index> SHRINK SPACE;

SHRINK SPACE requires the tablespace to use Automatic Segment Space Management. Neither releases space back to the OS or ASM disk group; both return space to the tablespace free pool for reuse by other segments in that tablespace. Both operations take locks on the index, so schedule them during a maintenance window on high-traffic objects.

Free space by dropping or truncating unrelated objects

Last resort when adding space is not immediately possible. Identify the largest segments in the same tablespace:

SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, BYTES/1048576 AS mb
FROM DBA_SEGMENTS
WHERE TABLESPACE_NAME = '<tablespace_name>'
ORDER BY BYTES DESC
FETCH FIRST 20 ROWS ONLY;

Only DROP or TRUNCATE returns extents to the tablespace free pool. DELETE does not lower the high-water mark and does not free extents for reuse by other segments. If you are tempted to DELETE rows to make room for an index extension, stop: it will not help.

Prevention

• Track utilization against maximum capacity, not current allocation. The denominator must include autoextend MAXSIZE. DBA_TABLESPACE_USAGE_METRICS handles this. Rolling your own from DBA_FREE_SPACE alone hides autoextend headroom and its absence.
• Alert on the largest single free extent, not just total free space. This catches fragmentation before it produces ORA-01654 on AUTOALLOCATE and UNIFORM tablespaces.
• Set explicit MAXSIZE on every autoextend datafile. Avoid MAXSIZE UNLIMITED except where you have independent monitoring of the underlying volume.
• Separate index tablespaces from data tablespaces operationally. Distinct tablespace names make capacity planning and alerting cleaner, even if you do not believe in physical separation for performance.
• Capacity plan using growth rate, not current utilization alone. Compute days_remaining = free_space / daily_growth_rate per tablespace. Alert when the projection drops below your planning threshold.
• Validate index rebuild target tablespaces before running the rebuild. A rebuild needs approximately the current index size of free space in the target tablespace. Failed rebuilds are a common cause of ORA-01654.
• Follow failed direct-path loads with DEALLOCATE UNUSED. Otherwise the segment keeps the extents allocated during the failed load.

How Netdata helps

• Per-second tablespace utilization. A bulk load or runaway segment shows up as a growth spike. Cross-reference with application deploy markers to identify which workload caused it.
• Correlation with redo generation, TPS, and active sessions. When ORA-01654 fires, see whether throughput dropped only on the failing path or across the instance. Distinguishes a single-segment failure from a broader hang.
• Underlying filesystem and disk monitoring. Host-level collectors surface the layer below Oracle: mount point utilization, disk I/O latency, and ASM disk group state. Autoextend failures often start here, not in Oracle’s views.
• Rate-of-change alerting. A tablespace at 70% that gained 15% in an hour is more urgent than one stable at 85% for weeks.
• Long-retention history for capacity planning. Days-to-full projections need weeks of clean data. Per-second history supports both immediate diagnosis and trend analysis.

For the integrated view, see Oracle Database monitoring with Netdata.

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