ORA-01555 “snapshot too old”


How to avoid ORA-01555 “snapshot too
old" error?

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
manipulation/committing here).
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

Leave a Reply

Your email address will not be published. Required fields are marked *