ORA-01555 with an undo tablespace that looks big enough

The message means: while reading a block for a query's snapshot SCN, Oracle needed a pre-change image from undo that had already been overwritten or expired.

Candidate causes here:

1. Retention not enforced. TUNED_UNDOSTAT below undo_retention is the tell: with best-effort retention, Oracle may reuse unexpired undo under space pressure. Because the file is fixed at 32 GB, concurrent batch DML can force exactly that. Test: compare MAXQUERYLEN against TUNED_UNDOSTAT and UNDOBLKS per interval; if tuned retention collapses during 20:00–02:00, that is the mechanism.
2. Query runs longer than retention covers. MAXQUERYLEN ≈ 11,000 s against retention 10,800 s is already marginal, and the report's wall-clock time is inflated by the COMMIT-per-1,000-rows logging, so the window during which undo must survive is longer than the query alone.
3. Delayed block cleanout. Test by checking whether the failing object was the target of recent heavy DML and whether the 1555 names blocks that were modified just before the query started.
4. Undo corruption or a genuinely inconsistent read from another cause - test with dbv/RMAN BACKUP VALIDATE and by reproducing in a controlled window.

A commit inside a fetch loop does not itself destroy the query's read consistency - the cursor's snapshot SCN is fixed at open. But it is a strong aggravating factor and often the practical cause:

• Each commit makes the fetch loop slower (log file sync, log writes, procedure call overhead), extending total elapsed time and therefore the span over which undo must remain available.
• If the procedure ever closes and reopens the cursor, or issues further statements in the loop, each gets a new snapshot, so the 'report' is no longer one consistent read - and the later statements may hit blocks whose undo is long gone.
• The commits also let the loop hold no undo of its own, so the DBA sees no correlation between the report and undo growth and mis-sizes the tablespace.

Remedy: open one cursor, do no DML or commits inside the loop, and write the log rows to a collection flushed at the end - or log to a separate autonomous transaction only if the logging can accept its own snapshot semantics. Then re-measure wall-clock time; it usually drops sharply, which by itself reduces the undo exposure.

RETENTION GUARANTEE (ALTER TABLESPACE undotbs1 RETENTION GUARANTEE) forbids reuse of unexpired undo. It converts the failure mode: instead of a query failing with ORA-01555, the DML fails with ORA-30036 (unable to extend undo segment). You are choosing which workload to hurt.

Because the tablespace is fixed at 32 GB and cannot autoextend, enabling the guarantee without sizing is dangerous - a 20:00–02:00 batch would simply stop writing.

Prerequisites:

1. Size from measurement: take peak UNDOBLKS per second from v$undostat, multiply by the required retention (peak query length plus margin), and compare to 32 GB. If the product exceeds it, you need a larger or autoextending undo tablespace before the guarantee.
2. Set undo_retention above the true worst-case query time, not just at it.
3. Make the undo tablespace autoextend with a sane MAXSIZE so DML degrades gracefully instead of halting, and monitor alerts for ORA-30036.

An alternative that avoids the trade-off entirely is to make the long report read a stable, less-contended source, or use Flashback Query (AS OF TIMESTAMP) with a bounded lookback so the required retention is explicit and small.