Oracle problems

Oracle Undo Tablespace Full: ORA-30036, What Holds the Undo, and When to Grow It

2 November 2026

An undo tablespace is supposed to look full. Oracle keeps old undo for consistent reads and flashback for as long as it has the space. ORA-30036 means it could not reuse any of it, and that is a different problem from a full datafile.

What the error says

ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'. A transaction needed another undo extent. The tablespace had no free extent and no expired extent to take, and the datafiles could not grow. The transaction rolls back, and if it ran for three hours, the rollback takes a while too.

Its sibling is ORA-01555: snapshot too old: a long query needed undo that a short transaction had already overwritten. The two errors pull in opposite directions. Keeping undo longer protects the queries and starves the transactions; reusing it sooner does the reverse. The fix for both is the same, which is why they share a page: enough undo for the retention you need, and no transaction that holds gigabytes of it.

Three kinds of undo

Status	Meaning	Can Oracle reuse it
ACTIVE	Held by an open transaction	Never, until it commits or rolls back
UNEXPIRED	Committed, younger than the undo retention	Yes, when nothing else is left, unless retention is guaranteed
EXPIRED	Committed, older than the undo retention	Yes, first

SELECT tablespace_name, status,
       ROUND(SUM(bytes) / POWER(1024, 3), 1) AS gb,
       COUNT(*) AS extents
FROM   dba_undo_extents
GROUP  BY tablespace_name, status
ORDER  BY tablespace_name, status;

A 50 GB undo tablespace with 48 GB of EXPIRED and UNEXPIRED undo is healthy. A 50 GB undo tablespace with 30 GB of ACTIVE undo has a transaction problem, and no datafile will fix it for long.

The transaction that holds it

SELECT s.inst_id, s.sid, s.serial#, s.username, s.module, s.status,
       s.sql_id, s.prev_sql_id, t.start_date,
       ROUND((SYSDATE - t.start_date) * 24, 1)        AS hours_open,
       ROUND(t.used_ublk * p.value / POWER(1024, 2))  AS undo_mb,
       t.used_urec                                    AS undo_records
FROM   gv$transaction t
JOIN   gv$session s ON s.inst_id = t.inst_id AND s.taddr = t.addr
CROSS JOIN (SELECT TO_NUMBER(value) AS value FROM v$parameter
            WHERE  name = 'db_block_size') p
ORDER  BY t.used_ublk DESC;

The usual shape: 20 GB of undo, open for six hours, STATUS INACTIVE, and PREV_SQL_ID pointing at an UPDATE. That is a batch that changes a whole table in one commit, or a session that changed rows and went home without a commit.

Killing it does not free the undo at once. The transaction rolls back first, which reads all of that undo again and can take about as long as the transaction took. USED_UBLK falls while it does.

How much undo you need

Oracle writes its own answer every ten minutes to V$UNDOSTAT, and keeps four days of it:

SELECT TO_CHAR(begin_time, 'DD HH24:MI') AS from_t,
       undoblks,
       ROUND(undoblks / 600) AS blocks_per_s,
       maxquerylen           AS longest_query_s,
       tuned_undoretention   AS tuned_retention_s,
       ssolderrcnt           AS ora_01555,
       nospaceerrcnt         AS ora_30036,
       unxpstealcnt          AS unexpired_taken
FROM   v$undostat
WHERE  begin_time > SYSDATE - 1
ORDER  BY begin_time DESC;

Three columns tell the story. ORA_30036 above zero is this page. ORA_01555 above zero is the other error. UNEXPIRED_TAKEN above zero means transactions already take undo that queries might still need: the next error is a snapshot too old, not a full tablespace.

The space you need is roughly the undo rate times the retention you want, and the retention must cover the longest query:

SELECT ROUND(MAX(u.undoblks) / 600 * r.value * b.value
             / POWER(1024, 3), 1)        AS undo_gb_for_retention,
       ROUND(MAX(u.maxquerylen) / 60)  AS longest_query_min
FROM   v$undostat u,
       (SELECT TO_NUMBER(value) AS value FROM v$parameter
        WHERE  name = 'undo_retention') r,
       (SELECT TO_NUMBER(value) AS value FROM v$parameter
        WHERE  name = 'db_block_size') b
GROUP  BY r.value, b.value;

Fix it, in this order

Find out which problem you have. ORA-30036 with a large ACTIVE share is a transaction. With a small ACTIVE share and RETENTION GUARANTEE, the guarantee blocks the reuse of unexpired undo, which is the trade you chose. With a datafile at its maximum size, it is space.
Let the datafiles grow, or add one. Both run online:
```
ALTER DATABASE DATAFILE '/u02/oradata/ORCL/undotbs01.dbf'
      AUTOEXTEND ON NEXT 1G MAXSIZE 32G;

ALTER TABLESPACE undotbs1
      ADD DATAFILE '/u02/oradata/ORCL/undotbs02.dbf' SIZE 10G
      AUTOEXTEND ON NEXT 1G MAXSIZE 32G;
```
With a fixed size, Oracle tunes the retention to the space it has and undo_retention is only a hint. With autoextend on, undo_retention is the minimum, and Oracle keeps more when it can.
Commit in batches. The 20 GB UPDATE becomes two hundred updates of 100 MB, by key range or by rowid range, with a commit after each. Every commit lets that undo expire. This is how Scops archives old rows: copy and delete in rowid chunks, one transaction each.
Set the retention from the numbers. The longest query plus a margin, not a habit: ALTER SYSTEM SET undo_retention = 3600; for a 45-minute report.
Guarantee only what you must. ALTER TABLESPACE undotbs1 RETENTION GUARANTEE; turns ORA-01555 into ORA-30036: queries always win, transactions fail. It exists for flashback requirements, not for comfort.

A larger undo tablespace can also replace the old one online: create it, run ALTER SYSTEM SET undo_tablespace = undotbs2;, and drop the old one when no transaction uses it any more.

See it coming

A monitor that alarms on the percentage used of an undo tablespace alarms on every healthy one, and then nobody reads it. Radar Engine, the detection layer of Scops, reads the status of the undo and the transaction behind it:

Detector	Clock	When it fires
Undo tablespace nearly full	Hourly, over 24 hours	High and held by an open transaction: Warning. Unexpired undo only: no finding.
Long-running transaction	Hourly	The largest open transaction at 3 hours, 2 GB of undo, and growing: Warning. 8 hours or 10 GB: Critical. Usual at this hour: Watch.

The line unexpired undo only: no finding is the point. With the OS credential of the database, the log detectors also report a repeated ORA-01555 from the alert log, which is the earlier sign of the same shortage.

Questions

My undo tablespace shows 99% used. Is that a problem?
Usually not. Oracle keeps expired undo for as long as there is space, so a healthy undo tablespace looks full. Check DBA_UNDO_EXTENTS: a problem is a large ACTIVE share, or NOSPACEERRCNT above zero in V$UNDOSTAT.

Why do I get ORA-30036 when most of the undo is UNEXPIRED?
The tablespace has RETENTION GUARANTEE. Without it, Oracle takes unexpired undo before it fails a transaction, and counts that in UNXPSTEALCNT.

Does killing the session free the undo at once?
No. The transaction rolls back first, which can take about as long as it took to run. USED_UBLK in V$TRANSACTION falls while it does.

Is undo_retention in seconds?
Yes. The default is 900 seconds. With autoextend on, it is the minimum that Oracle keeps. With a fixed size, Oracle tunes the retention to the space, and the parameter is a lower bound only with RETENTION GUARANTEE.

Sources

Oracle: ORA-30036, ORA-01555
Oracle: V$UNDOSTAT, DBA_UNDO_EXTENTS, V$TRANSACTION
Oracle: Managing Undo
Scops docs: Detector reference