Troubleshooting ORA-01555 Snapshot Too Old Errors in Oracle EBS

One of our finance users came to me today complaining that a critical end-of-month report kept failing halfway through. Looking at the request log, the culprit was obvious: ORA-01555: snapshot too old: rollback segment number with name... too small.

1. Understanding the Failure

This error simply means a long-running query needed to see old data blocks for consistency, but those blocks were overwritten in the Undo tablespace before the query could finish. To find out exactly how long the query was running and check our baseline retention times, I ran this diagnostic check:

SELECT tuned_undoretention, maxquerylen, undoblks FROM v$undostat;

2. The Live Production Fix

The maximum query length was heavily exceeding our default undo retention window parameters. To stop this from killing long finance reports, I dynamically extended our data parameters and scaled up our space bounds directly on the live database server node:

ALTER SYSTEM SET undo_retention=10800 SCOPE=BOTH;
ALTER DATABASE DATAFILE '/u01/oradata/prod/undo01.dbf' RESIZE 10G;

Setting `undo_retention` to 10800 forces Oracle to hold onto historical database undo blocks for a minimum of 3 hours. The user re-ran the processing transaction, and it completed successfully without a single snapshot dropout. Keep an eye on your monthly undo sizing!