ORA-01555: snapshot too old
We were doing stress testing in PeopleSoft ELM and ran the Mass Enrollment AppEngine. After running for about 90 minutes, it failed with the error ORA-01555: snapshot too old: rollback segment number 19 with name “_SYSSMU19_1270741541$” too small
The ORA-01555 error is caused by Oracle read consistency mechanism and is usually associated with long running UPDATE SQL and too small UNDO. This is because of the way Oracle has been designed – You must also have an UNDO tablespace that’s large enough to handle the amount of UNDO you will be generating/holding.
“ORA-01555: Snapshot too old, rollback segment too small” errors. if you have a long running SQL that starts at 10:10 AM, Oracle ensures that all rows are as they appeared at 10:10 AM, even if the query runs until midnight!
In my case, the solution was easy – I was trying to process about 50,000 applicants and also, I had trace turned on.
For other scenarios, the ORA-01555 snapshot too old error can be addressed by one or more options listed below.
Avoiding ORA-01555: snapshot too old error
- Avoide transactions while sensitive (like UPDATE, INSERT) queries or transactions are running.
- Increase commit frequency or don’t fetch between commits.
- Modifying or customizing the program as a series of restartable steps.
- Re-schedule long-running queries when the system has lesser DML load like off-shift hours.
- Increase the rollback segment (undo) size. The ORA-01555 snapshot too old also relates to your setting for automatic undo retention.
