Managing Undo Data
Scope
This domain covers undo tablespaces, undo segments, retention, guarantee, flashback query dependencies, and ORA-01555. Questions often ask why undo cannot be reused or why a query fails.
Core rules
- UNDO_MANAGEMENT is AUTO in Oracle 19c; UNDO_TABLESPACE names the active undo tablespace. Undo records before-images for rollback, read consistency, and flashback features.
- UNDO_RETENTION sets the desired retention in seconds. RETENTION GUARANTEE prevents unexpired undo from being overwritten; RETENTION NOGUARANTEE allows reuse. Guarantee can make DML fail with ORA-30036 if the tablespace is too small.
- V$UNDOSTAT shows undo consumption and tuning advice; DBA_UNDO_EXTENTS shows extent status; V$TRANSACTION maps transactions to undo segments.
- Flashback Query, Flashback Table, and Flashback Database rely on undo or flashback logs; they are not the same mechanism.
Worked example
ALTER SYSTEM SET undo_retention=3600; ALTER TABLESPACE undotbs1 RETENTION GUARANTEE; Then a long-running query can use one hour of undo, but if the undo tablespace fills, DML may fail rather than overwrite unexpired undo. Check V$UNDOSTAT for tuned UNDBLKS and MAXQUERYLEN.
Traps
UNDO_RETENTION is not a guarantee by itself. An undo tablespace cannot be dropped while in use. ORA-01555 is a snapshot-too-old error, not a space error. Flashback Database uses flashback logs, not only undo.