ORA-01555 ERROR MESSAGE “SNAPSHOT TOO OLD”

ORA-01555 ERROR MESSAGE “SNAPSHOT TOO OLD”

Cause: Rollback records needed by a reader for consistent read are overwritten by other writers.

Action: If in Automatic Undo Management mode, increase the setting of UNDO_RETENTION. Otherwise, use larger rollback segments.

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.

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.

Undo Retention

After a transaction is committed, undo data is no longer needed for rollback or transaction recovery purposes. However, for consistent read purposes, long-running queries may require this old undo information for producing older images of data blocks.

For these reasons, it is desirable to retain the old undo information for as long as possible.

When automatic undo management is enabled, there is always a current undo retention period, which is the minimum amount of time that Oracle Database attempts to retain old undo information before overwriting it.

Old (committed) undo information that is older than the current undo retention period is said to be expired.

Old undo information with an age that is less than the current undo retention period is said to be unexpired.

When available space for new transactions becomes short, the database begins to overwrite expired undo.

If the undo tablespace has no space for new transactions after all expired undo is overwritten,

the database may begin overwriting unexpired undo information.

If any of this overwritten undo information is required for consistent read in a current long-running query, the query could fail with the snapshot too old error message.

The following points explain the exact impact of the UNDO_RETENTION parameter on undo retention:

The UNDO_RETENTION parameter is ignored for a fixed size undo tablespace. The database may overwrite unexpired undo information when tablespace space becomes low.

For an undo tablespace with the AUTOEXTEND option enabled, the database attempts to honor the minimum retention period specified by UNDO_RETENTION. When space is low, instead of overwriting unexpired undo information, the tablespace auto-extends. If the MAXSIZE clause is specified for an auto-extending undo tablespace, when the maximum size is reached, the database may begin to overwrite unexpired undo information.

UNDO_RETENTION can only be honored if the current undo tablespace has enough space.

Retention Guarantee

To guarantee the success of long-running queries or Oracle Flashback operations, you can enable retention guarantee. If retention guarantee is enabled, the specified minimum undo retention is guaranteed; the database never overwrites unexpired undo data even if it means that transactions fail due to lack of space in the undo tablespace. If retention guarantee is not enabled, the database can overwrite unexpired undo when space is low, thus lowering the undo retention for the system. This option is disabled by default.