ORA-01555 on a 55-minute report with a 20 GB undo tablespace
GLBATCH is a 24/7 order-processing database running with UNDO_MANAGEMENT=AUTO. UNDOTBS1 is 20 GB with AUTOEXTEND OFF, UNDO_RETENTION=900, and retention guarantee is disabled. A nightly revenue report runs one 55-minute query against ORD_LINE (900 million rows). It has failed with ORA-01555 for four consecutive nights, always between minute 30 and minute 40; the error names the undo segment owned by a purge job that deletes and commits every 500 rows from a different table. V$UNDOSTAT for the peak hour shows MAXQUERYLEN=3300, TUNED_UNDORETENTION around 3400, and undo tablespace usage peaking at 19.6 GB. AWR also shows a large indexed bulk load running in that same hour. The mount holding the undo datafile has 400 GB free. Finance refuses to shorten the report. The platform owner refuses to enable retention guarantee because "a database must never stop on space".
1. Why did ORA-01555 occur with AUTO undo management and a 900-second retention setting? What do MAXQUERYLEN and TUNED_UNDORETENTION tell you?
Read the worked answer
UNDO_MANAGEMENT=AUTO does not mean unlimited retention. With guarantee off, Oracle keeps undo for as long as space allows and treats UNDO_RETENTION (900 s) as the floor it aims for. When the undo tablespace cannot grow - 20 GB, AUTOEXTEND OFF - and new transactions need space, Oracle reuses unexpired undo blocks. The report's consistent read then needs a pre-image that no longer exists, and you get ORA-01555.
The statistics confirm this rather than contradicting it:
MAXQUERYLEN = 3300: the longest query needed undo history spanning 3300 seconds - that is your report, not the 900 s setting.TUNED_UNDORETENTION ≈ 3400: what Oracle wanted to keep. The 900 s value was irrelevant because auto-tuning wanted far more.- Peak usage 19.6 GB of 20 GB: the tablespace was physically incapable of holding 3400 seconds of undo while the purge job and bulk load were also generating undo, so retention was silently violated at minute 30-40.
The root cause is space, not a mis-set parameter. The aggravating factor is that the heaviest undo generators run inside the exact window the report needs stability.
2. Size the undo tablespace so the report survives without retention guarantee, and state the residual risk. Show the arithmetic you would use with the numbers available.
Read the worked answer
Method: required undo = (undo generated per second during the report window) x (required retention seconds) x headroom.
- Take the undo generation rate from
V$UNDOSTAT.UNDOBLKSfor the peak interval (multiply by the undo block size and divide by the interval length), or use the observed 19.6 GB for that hour. - Worked example: assume the peak window generated about 6 MB/s.
3400 s x 6 MB/sis roughly 20.4 GB of undo that must remain unexpired, plus room for active transactions and normal churn. - A defensible target is about 30-40 GB, deployed as
ALTER DATABASE DATAFILE '.../undotbs01.dbf' AUTOEXTEND ON NEXT 1G MAXSIZE 40G;- the 400 GB mount easily accommodates it - or as an additional 16 GB datafile.
Order matters: resize first, then decide about guarantee. If guarantee stays off, the residual risk is that any future undo spike (a larger bulk load, a new batch, a heavier purge) can still force ORA-01555; the change removes the current cause but cannot promise the future. The other lever, if space were unavailable, is to reduce demand: move the purge job and the bulk load out of the report window so TUNED_UNDORETENTION fits in the space the tablespace actually holds.
3. What would happen if retention guarantee were enabled on the current 20 GB tablespace, and how do ORA-30036 and ORA-01555 differ?
Read the worked answer
ORA-01555 is a reader failure: a query's consistent read needs a pre-image that has already been overwritten. ORA-30036: unable to extend segment by N in undo tablespace is a writer failure: with guarantee on, undo cannot be overwritten or extended beyond the tablespace, so the DML statement fails and its transaction rolls back.
With guarantee enabled on a 20 GB tablespace that needs ~3400 seconds of undo during the peak hour, the report would probably stop failing - but the purge job and the bulk load would start failing with ORA-30036, because 20 GB cannot hold the required unexpired undo plus active transactions. Guarantee does not create space; it converts a silent retention violation into a hard error at the writer. That is exactly why the platform owner's objection is legitimate and why the resize must come first.
Correct sequence: resize to ~40 GB maximum, observe one or two successful runs (MAXQUERYLEN stable, TUNED_UNDORETENTION comfortably below held space), then enable guarantee and add alerting on undo tablespace usage via DBA_OUTSTANDING_ALERTS and tablespace threshold alerts.