A session executing DML fails with ORA-30036: unable to extend segment by N in undo tablespace. The transaction rolls back and any other session needing undo for the same tablespace also fails. Unlike ORA-01555 (“snapshot too old”), which is a read-path failure, ORA-30036 is a write-path failure: Oracle cannot find space to record the before-image of a data change.

The error is cliff-edge. The undo tablespace is the shared resource every DML writes to. When it is exhausted by ACTIVE undo (undo records belonging to uncommitted transactions that Oracle cannot overwrite), no new DML can record its undo and the operation fails immediately.

The canonical monitoring signal is V$UNDOSTAT.NOSPACEERRCNT. Any non-zero value in a 10-minute interval means transactions have already failed with ORA-30036 during that interval.

What this means

Undo extents live in three states:

  • ACTIVE - undo for uncommitted transactions. Cannot be reclaimed under any circumstance.
  • UNEXPIRED - older than the active transaction needs but younger than UNDO_RETENTION. Reclaimable under space pressure unless RETENTION GUARANTEE is on.
  • EXPIRED - older than UNDO_RETENTION. Free for reuse.

ORA-30036 fires when Oracle needs to allocate a new ACTIVE extent and cannot: ACTIVE already fills the tablespace, UNEXPIRED is protected by RETENTION GUARANTEE, and EXPIRED has been consumed. This is distinct from ORA-01555, which fires when a long-running read needs undo that has already been overwritten.

flowchart TD
    A[DML writes undo record] --> B{Extend ACTIVE extent?}
    B -- yes, space available --> C[Allocate, continue]
    B -- no free EXPIRED --> D{Steal UNEXPIRED?}
    D -- yes, no GUARANTEE --> E[Reuse extent]
    E --> C
    D -- RETENTION GUARANTEE on --> F[Cannot reuse]
    D -- no UNEXPIRED left --> F
    F --> G[ORA-30036: DML fails]

Common causes

CauseWhat it looks likeFirst thing to check
One huge uncommitted transactionV$TRANSACTION shows one row with USED_UBLK orders of magnitude larger than the rest; session may be INACTIVEQuery V$TRANSACTION ordered by USED_UBLK DESC
RETENTION GUARANTEE on undersized tablespaceDBA_UNDO_EXTENTS shows high UNEXPIRED, low ACTIVE; DBA_TABLESPACES.RETENTION = GUARANTEECheck DBA_TABLESPACES.RETENTION for the undo tablespace
Undersized undo tablespaceACTIVE undo > 85% of tablespace; UNXPSTEALCNT > 0 in V$UNDOSTAT historyDBA_TABLESPACE_USAGE_METRICS for the undo tablespace
Smallfile datafile at 32GB ceilingOne datafile, AUTOEXTENSIBLE=YES but stuck at ~32GB with 8KB blocks; used_pct_of_max = 100DBA_DATA_FILES, count files and inspect MAXBYTES
ALTER TABLE SHRINK SPACE or bulk DDLFailed operation in session; high undo generation rate over a short windowAlert log around the failure time

Quick checks

# Confirm ORA-30036 has been firing and over what window
sqlplus -S / as sysdba <<'SQL'
SELECT BEGIN_TIME, END_TIME, UNDOBLKS, MAXQUERYLEN,
       UNXPSTEALCNT, SSOLDERRCNT, NOSPACEERRCNT
FROM V$UNDOSTAT
WHERE BEGIN_TIME > SYSDATE - 1
ORDER BY BEGIN_TIME DESC
FETCH FIRST 24 ROWS ONLY;
SQL

-- Undo extent state: ACTIVE should be small in a healthy system
SELECT STATUS, SUM(BYTES)/1048576 AS mb, COUNT(*) AS extents
FROM DBA_UNDO_EXTENTS
GROUP BY STATUS
ORDER BY mb DESC;

-- Largest undo consumers right now
SELECT s.SID, s.SERIAL#, s.USERNAME, s.SQL_ID, s.STATUS,
       t.USED_UBLK,
       ROUND(t.USED_UBLK * (
         SELECT VALUE FROM V$PARAMETER WHERE NAME = 'db_block_size'
       ) / 1048576, 1) AS undo_mb
FROM V$TRANSACTION t
JOIN V$SESSION s ON t.SES_ADDR = s.SADDR
ORDER BY t.USED_UBLK DESC;

-- Confirm whether RETENTION GUARANTEE is forcing ORA-30036
SELECT TABLESPACE_NAME, RETENTION, CONTENTS
FROM DBA_TABLESPACES
WHERE CONTENTS = 'UNDO';

-- Undo tablespace capacity vs usage, including autoextend
SELECT df.TABLESPACE_NAME,
       ROUND(df.TOTAL_MB, 1) AS current_mb,
       ROUND(df.MAX_MB, 1) AS max_mb,
       ROUND(NVL(fs.FREE_MB, 0), 1) AS free_mb,
       ROUND((df.TOTAL_MB - NVL(fs.FREE_MB,0)) / df.MAX_MB * 100, 1) 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
WHERE df.TABLESPACE_NAME IN (SELECT TABLESPACE_NAME FROM DBA_TABLESPACES WHERE CONTENTS = 'UNDO');

How to diagnose it

  1. Confirm the error source. ORA-30036 should correlate with V$UNDOSTAT.NOSPACEERRCNT > 0 in the same 10-minute interval. If the application reports ORA-30036 but NOSPACEERRCNT is zero, the error came from a different instance (RAC) or a different time window. Widen the query.

  2. Characterize what is filling the tablespace. Compare ACTIVE, UNEXPIRED, and EXPIRED from DBA_UNDO_EXTENTS. ACTIVE dominant means uncommitted transactions. UNEXPIRED dominant with low ACTIVE and RETENTION = GUARANTEE means the guarantee is blocking reuse. EXPIRED low or zero means the tablespace is genuinely undersized.

  3. Identify the largest active transaction. The V$TRANSACTION query ranks sessions by USED_UBLK. A single transaction consuming most of the undo tablespace is the most common pattern. Note whether the holding session is ACTIVE or INACTIVE: an INACTIVE session holding gigabytes of undo is typically a developer tool or an abandoned batch with autocommit off.

  4. Check the retention setting. SELECT VALUE FROM V$PARAMETER WHERE NAME = 'undo_retention' returns the configured retention in seconds (default 900). The actual retention Oracle honors is V$UNDOSTAT.TUNED_UNDORETENTION, which can be higher when space allows. If retention is high relative to the undo generation rate, the tablespace must be larger.

  5. Check datafile headroom. A smallfile tablespace with a single datafile caps at roughly 32GB with the default 8KB block size (4M blocks times block size), even with AUTOEXTENSIBLE=YES and MAXSIZE UNLIMITED. Confirm via DBA_DATA_FILES how many datafiles exist and whether they are at MAXSIZE.

  6. In RAC, repeat on every instance. Each instance has its own undo tablespace and its own V$UNDOSTAT history. A failure on one node is not visible in another instance’s view.

Metrics and signals to monitor

SignalWhy it mattersWarning sign
V$UNDOSTAT.NOSPACEERRCNTCounts ORA-30036 events per 10-minute intervalAny value > 0 means writes are already failing
V$UNDOSTAT.SSOLDERRCNTCounts ORA-01555 events per interval> 0 means undo pressure has crossed into read-path failure
V$UNDOSTAT.UNXPSTEALCNTCounts attempts to steal UNEXPIRED extentsConsistently > 0 means undo is undersized for current retention
DBA_UNDO_EXTENTS STATUS distributionShows what is consuming the tablespaceACTIVE > 60% of capacity is PAGE risk
V$TRANSACTION.USED_UBLK per sessionIdentifies the transaction holding the undoOne session orders of magnitude larger than the rest
DBA_TABLESPACES.RETENTIONWhether GUARANTEE is onGUARANTEE on an undersized tablespace converts ORA-01555 into ORA-30036
Undo tablespace used_pct_of_maxCapacity headroom including autoextend> 85% is TICKET, > 95% with no autoextend is PAGE
V$UNDOSTAT.MAXQUERYLENLongest query in each intervalApproaching or exceeding UNDO_RETENTION means ORA-01555 risk

Fixes

One huge uncommitted transaction

The fastest relief is to terminate the offending transaction. Killing the session rolls back its undo and releases the ACTIVE extents. This is disruptive: the application must handle the rollback, and rollback itself is I/O intensive.

-- Identify the session, then:
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

If the transaction cannot be killed because it is doing legitimate work, the only option is to add undo space faster than the transaction consumes it. Do not commit a partial transaction just to release undo; commit boundaries are an application-logic decision.

RETENTION GUARANTEE on an undersized tablespace

RETENTION GUARANTEE is a deliberate trade-off: it guarantees long-running reads will find their undo, at the cost of converting read-path ORA-01555 into write-path ORA-30036 when the tablespace is undersized.

To restore write availability immediately:

ALTER TABLESPACE <undo_tablespace> RETENTION NOGUARANTEE;

This is reversible and non-destructive. Once space pressure clears, evaluate whether to re-enable GUARANTEE with a properly sized tablespace.

Undersized undo tablespace

Add a datafile or grow an existing one. Adding a datafile sidesteps the smallfile 32GB ceiling and is safer than relying on a single autoextending file.

ALTER TABLESPACE <undo_tablespace>
ADD DATAFILE '<path>/undotbs2.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 32G;

Or resize an existing datafile:

ALTER DATABASE DATAFILE '<path>/undotbs1.dbf' RESIZE 8G;
ALTER DATABASE DATAFILE '<path>/undotbs1.dbf' AUTOEXTEND ON MAXSIZE 32G;

For systems routinely hitting the smallfile ceiling, consider creating a new bigfile undo tablespace and switching.

CREATE BIGFILE TABLESPACE undotbs2
DATAFILE '<path>/undotbs2.dbf' SIZE 32G AUTOEXTEND ON;
ALTER SYSTEM SET undo_tablespace = undotbs2 SCOPE=BOTH;

Switching undo tablespaces is safe while the instance is up. Existing transactions continue against the old tablespace until they commit.

Tuning UNDO_RETENTION

If undo pressure is chronic, do not lower UNDO_RETENTION blindly. Lowering it trades ORA-30036 for ORA-01555: long-running reads start failing instead of writes. The correct response is to size the tablespace for both the ACTIVE undo load and the retention requirement.

A rough sizing estimate:

undo_size_bytes = peak_undo_blocks_per_second * max(retention_seconds, longest_query_seconds) * db_block_size

Use V$UNDOSTAT.UNDOBLKS deltas at peak to estimate the rate, and V$UNDOSTAT.MAXQUERYLEN for the longest query. Aim for ACTIVE undo at peak to stay below 60% of tablespace capacity.

Prevention

  • Track UNXPSTEALCNT. It is the leading indicator. Consistently non-zero values mean undo pressure is happening before any error surfaces.
  • Track ACTIVE undo as a percentage of the undo tablespace during peak windows. Over 60% is the warning band; 85% is the cliff.
  • Verify RETENTION GUARANTEE matches capacity. GUARANTEE on a tight tablespace converts ORA-01555 into ORA-30036. Decide deliberately which failure mode the business prefers.
  • Monitor for long-running uncommitted sessions. A session holding TX locks with USED_UBLK disproportionate to the workload is the most common root cause.
  • Use bigfile or multi-datafile undo tablespaces on high-throughput systems. The smallfile 32GB ceiling is a frequent surprise.
  • In RAC, monitor per-instance. Each instance has its own undo tablespace and its own V$UNDOSTAT history.
  • In multitenant, verify UNDO_RETENTION propagation. Since 19.9.0, UNDO_RETENTION set in CDB$ROOT is no longer inherited by PDBs; use ALTER SYSTEM SET UNDO_RETENTION=... CONTAINER=ALL to propagate.

How Netdata helps

Netdata surfaces per-second metrics that close the gap between “undo is fine” and “ORA-30036 is firing”:

  • Correlate NOSPACEERRCNT and SSOLDERRCNT deltas with active session counts to see whether write-path failures coincide with a load spike or a single long transaction.
  • Track undo tablespace utilization including autoextend headroom, so a single datafile stuck at the smallfile ceiling is visible before the error fires.
  • Watch transaction growth indirectly through redo generation rate and commit latency: a runaway transaction generating undo also generates redo, and log file sync begins to move before the undo tablespace fills.
  • Alert on UNXPSTEALCNT trending non-zero across multiple 10-minute windows, which is the early warning before NOSPACEERRCNT goes non-zero.
  • Per-instance dashboards for RAC, so an undo pressure event on one node is not masked by healthy aggregate views.
  • Anomaly detection on undo block generation rate catches batch jobs and ALTER TABLE SHRINK SPACE operations before they exhaust undo.

See Oracle Database monitoring with Netdata for the full integration.