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
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
UPDATEorDELETEin 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
-
Change the data in batches with commits between them, or use
CREATE TABLE AS SELECTand swap tables for very large rewrites. -
Let the undo tablespace grow.
ALTER DATABASE DATAFILE '/u01/oradata/ORCL/undotbs01.dbf' AUTOEXTEND ON MAXSIZE 32G;
-
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
ORA-01555snapshot too oldORA-01652unable to extend temp segment9002SQL Server: Transaction log for database is full
More space, memory and limits errors
ORA-01653unable to extend table in tablespaceORA-01950no privileges on tablespaceORA-04031unable to allocate bytes of shared memoryORA-01000maximum open cursors exceededORA-00018maximum number of sessions exceededORA-00020maximum number of processes exceededORA-00257archiver error
The code and message text are from the Oracle Database Error Help. Browse the full Oracle error list or search every database error.
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.