Oracle / PLSQL: ORA-01555 Error Message

Learn the cause and how to resolve the ORA-01555 error message in Oracle.

Description

When you encounter an ORA-01555 error, the following error message will appear:

• ORA-01555: snapshot too old (rollback segment too small)

Cause

This error can be caused by one of the problems, as described below.

Resolution

The option(s) to resolve this Oracle error are:

Option #1

This error can be the result of there being insufficient rollback segments.

A query may not be able to create the snapshot because the rollback data is not available. This can happen when there are many transactions that are modifying data, and performing commits and rollbacks. Rollback data is overwritten when the rollback segments are too small for the size and number of changes that are being performed.

To correct this problem, make more larger rollback segments available. Your rollback data for completed transactions will be kept longer.

Option #2

This error can be the result of programs not closing cursors after repeated FETCH and UPDATE statements.

To correct this problem, make sure that you are closing cursors when you no longer require them.

Option #3

This error can occur if a FETCH is executed after a COMMIT is issued.

The number of rollback records created since the last CLOSE of your cursor will fill the rollback segments and you will begin overwriting earlier records.