ORA-01555 on a Six-Hour Report With a 195 GB Undo Tablespace
REPORTDB runs Oracle 19c with a 120 GB undo tablespace that has AUTOEXTEND ON, NEXT 1 GB and MAXSIZE 200 GB. undo_retention is 10800 seconds and retention guarantee is not enabled. A month-end report joins three large tables, loops over about 9 million rows, and commits every 50,000 rows from an autonomous transaction; it runs for up to 6 hours. It fails near the end with ORA-01555: snapshot too old: rollback segment number 12 with name "SYSSMU12...". After the failure the undo tablespace has grown to 195 GB. V$UNDOSTAT for the failing periods shows MAXQUERYLEN around 21,400 seconds and TUNED_UNDORETENTION around 11,000 seconds. A second, much shorter query against the ORDERS table also raises ORA-01555 occasionally; ORDERS is the target of a concurrent job that deletes and reinserts 40 million rows just before the query starts.
1. Why did a 195 GB undo tablespace with autoextend not prevent the ORA-01555 on the long report?
Read the worked answer
Space was never the binding constraint; time was. ORA-01555 is raised when a query needs a consistent read image that has already been overwritten, and Oracle only protects undo for the retention period it is actually honoring, not for the query's real runtime. Here the report runs about 21,400 seconds while TUNED_UNDORETENTION is about 11,000 seconds, so undo older than that becomes expired and eligible for overwrite no matter how much free space exists. Autoextend and a 200 GB maxsize simply let the tablespace grow to 195 GB while still overwriting the images the report needs.
The absence of retention guarantee is the reason Oracle is allowed to expire undo early. With guarantee on, the instance would refuse to overwrite unexpired undo and DML would instead fail with ORA-30036, which is a deliberate trade: a report failing versus transactions failing.
Also note MAXQUERYLEN is sampled from V$UNDOSTAT in ten-minute buckets and reflects the longest query in that bucket; read the maximum across the whole window, not the most recent row. DBA_HIST_UNDOSTAT gives the historical view across the month-end run.
2. Rank the remedies for the long report and state the risk each one carries.
Read the worked answer
First, align retention with reality: raise undo_retention to cover the measured longest query plus a margin, for example 25,000 to 28,000 seconds, and size the tablespace for the redo volume that implies rather than assuming the current 120 GB is enough. Then enable RETENTION GUARANTEE during the month-end window, because guarantee without a correctly sized tablespace converts a reporting outage into transaction failures with ORA-30036.
Second, attack the query itself, because it is cheaper than carrying undo for six hours. The commits from the autonomous transaction do not help the read-consistent snapshot and complicate error handling; removing them is usually correct unless the loop must be restartable. Materializing intermediate results into a work table, or using DBMS_FLASHBACK or AS OF SCN on a stable snapshot with a single query, avoids holding one six-hour snapshot.
Third, for the short query on ORDERS, the cause is different: rows modified by the concurrent job were committed before the query touched those blocks, so the query must apply delayed block cleanout and needs undo that has already aged out. Remedies are to gather statistics or otherwise force cleanout after the load, to avoid starting the query immediately after the load commits, and to check whether the query uses an index that causes many block visits.
3. How would you size undo for the new retention before switching the guarantee on, and how would you verify it is safe?
Read the worked answer
Size from measured conversion, not from the current allocation. From V$UNDOSTAT take UNDOBLKS per interval, convert to bytes using the block size, and divide by the interval length to get an undo generation rate in bytes per second. Multiply that peak rate by the target retention in seconds, then add overhead for expired and active extents and for delayed block cleanout, typically 20 to 50 percent. Cross-check against DBA_HIST_UNDOSTAT across at least two month-end cycles so a single atypical run does not size the tablespace.
Before enabling guarantee, set the new undo_retention and let the tablespace grow under monitoring for a full cycle, watching V$UNDOSTAT.MAXQUERYLEN and the alert log. Confirm the peak duration you are protecting against, and confirm the tablespace can physically reach that size on the storage, including any maxsize clause. Then enable guarantee outside the reporting window and set thresholds on undo space usage and on ORA-30036 in the alert log.
Have a documented rollback: disabling guarantee is instant, and if DML starts failing you know within minutes that the sizing was optimistic, which is far better than discovering it six hours into a report.