// Oracle error reference

ORA-30036: unable to extend segment in undo tablespace

A transaction needed more undo space than the undo tablespace could give, usually a single huge UPDATE or DELETE.

Error code
ORA-30036
Category
Space, memory and limits
Error message
unable to extend segment by string in undo tablespace 'string'

What it means

Every change a transaction makes is recorded in undo until it commits. ORA-30036 means the undo tablespace couldn't grow to hold more for this transaction. The failing statement is rolled back.

Common causes

  • One very large UPDATE or DELETE in a single transaction.
  • An undo tablespace that is too small or can't autoextend.
  • Long-running transactions that hold undo for a long time.

How to fix it

  1. Change the data in batches with commits between them, or use CREATE TABLE AS SELECT and swap tables for very large rewrites.

  2. Let the undo tablespace grow.

    ALTER DATABASE DATAFILE '/u01/oradata/ORCL/undotbs01.dbf' AUTOEXTEND ON MAXSIZE 32G;
  3. Look for old open transactions holding undo.

    SELECT s.sid, s.username, t.start_time, t.used_ublk
    FROM v$transaction t JOIN v$session s ON s.taddr = t.addr
    ORDER BY t.start_time;

Related errors

More space, memory and limits errors

The code and message text are from the Oracle Database Error Help. Browse the full Oracle error list or search every database error.

// fewer errors, faster fixes

Work on Oracle in SQLly

A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for Oracle and the other databases you use.