ORA-01555 ERROR MESSAGE “SNAPSHOT TOO OLD”

This message appears as a result of an Oracle read consistency mechanism. While your query begins to run, the data may be simultaneously changed by other people accessing the data. Oracle cannot access the original copy of the data from when the query started, and the changes cannot be undone by Oracle as they are made. Both committed versions of blocks and uncommitted versions of blocks are maintained to ensure that queries can access the data as it exists in the database at the time of the query. This is referred to as “consistent read” blocks and is maintained by Oracle Automatic Undo Management (AUM).

For example, you may begin your SQL query at 1:00 PM, yet at the same hour, another user may be making changes to the data from another computer. If this occurs, you may encounter error ORA-01555 because the results outputted by Oracle must contain data as it appeared at 1:00PM before changes were made by the other user.

ORA-01555 relates to insufficient rollback segments or undo_retentions parameter values that are not large enough. The modified data by performed commits and rollbacks causes rollback data to be overwritten when the rollback segments are smaller in size and number of the changes being performed at the time.

To resolve this issue, either increase the parameter of UNDO_RETENTION if you are in AUM mode or use larger rollback segments. The latter solution will allow your rollback data for completed transactions to be kept longer.

You may also run into this error when cursors are not being in programs after FETCH and UPDATE statements. Make sure you are closing cursors when you no longer need them. The error can also appear if a FETCH statement is run after a COMMIT statement is issued. If this occurs, you will begin to overwrite earlier records because the number of rollback records created since the last CLOSE will fill the rollback segments.

In summary, follow these practices to avoid seeing error ORA-01555 in the future:

• Do not run discrete queries and sensitive queries simultaneously unless the data is mutually exclusive.
• If possible, schedule queries during off-peak hours to ensure consistent read blocks do not need to rollback changes.
• Use large optimal values for rollback segments.
• Use a large database block size to maximize rollback segment transaction table slots.
• Reduce transaction slot reuse by performing less commits, especially in PL/SQL queries.
• Avoid committing inside a cursor loop.
• Do not fetch between commits, especially if the data queried by the cursor is being changed in the current session.
• Optimize queries to read fewer data and take less time to reduce the risk of consistent get rollback failure.
• Increase the size of your UNDO tablespace, and set the UNDO tablespace in GUARANTEE mode.
• When exporting tables, export with CONSISTENT = no parameter.