How to avoid ORA-01555 “snapshot too
From the developer’s
Restructure your PL/SQL code to
avoid fetching across commits that cause the ORA-01555 error.
One possible reason can be if we
leave the cursor open for fetching while
we are processing and committing data changes for a long time.
To eliminate this error
completely-you need to modify the code in the following manner:
1. Opens the cursor.
2. Fetches the whole cursor into the collection (no data
3. Closes the cursor.
4. Loops through the collection, processes the data, and commits.
From the DBA’s perspective:
Avoid error by allocating rollback
segments and configuring them with proper parameter values.
For example see: http://www.devx.com/getHelpOn/10MinuteSolution/16600