ORA-1555: Snapshot too old的解决

遇到ORA-1555问题,通过查询WEB,已解决问题,特将以下文章推荐给各位

Rollback Segments Overview

• Rollback Segments allow users to undo, or rollback, DML transactions that have been made against a database.
• DML transactions typically are composed of SQL deletes, inserts and updates.
• The life of a transaction is from the beginning of a program to a commit or from a commit to the next commit.
• Rollback segments allow the database to maintain read consistency among multiple transactions.
• A rollback segment entry is the set of before-image data blocks that contain rows which have been modified by a transaction.
• Each transaction must be completely contained within one rollback segment.
• A single rollback segment can hold multiple transactions.
• A transaction can not expand into any other rollback segments.
• Transactions are assigned to rollback segments by the database in a round-robin fashion.

Space Usage Within Rollbacks

• When a transaction begins, Oracle starts writing before-images into a rollback segment.
• Transactions (the before-image data) cannot expand into any other rollback segments, nor can it dynamically switch to use a different rollback segment.
• Oracle writes transactions sequentially to an extent within the rollback segment.
• Each transaction writes to only one extent of the rollback segment at any given time.
• Many active transactions can write concurrently to a single rollback segment --even the same extent of a rollback segment-- however, each data block in a rollback segment's extent can contain information for only a single transaction.
• When a transaction runs out of space in the current extent and needs to continue writing, Oracle finds an available extent in the same rollback segment in one of two ways:

1.      Reuse an extent already allocated to the rollback segment.

2.      Acquire (and allocate) a new extent to the rollback segment.

• The first transaction that needs to acquire more rollback space, checks the next extent of the rollback segment. If the next extent of the rollback segment does not contain information from an active transaction, Oracle makes it the current extent. All transactions that need more space from then on can write rollback information to the new current extent.
• A commit transaction signals Oracle to release the rollback information but does not immediately destroy it. The information remains in the rollback segment to create read-consistent views of data for queries that started before the transaction committed.
• When the last extent of the rollback segment becomes full, Oracle continues writing rollback data by wrapping around to the first extent in the segment.

Illustrated Use of Extents in a Rollback Segment

Two transactions, T1 and T2, which begin writing in the third extent (E3) and are now actively writing to the fourth extent (E4) of a rollback segment.

When the current extent (E4) fills, Oracle checks the next extent allocated to the rollback segment to determine if it is available. T1 and T2 continue writing to the next available extent, E1.

ORA-1555: Snapshot too old

The usual cause of this error is that long-running transactions/queries are occurring within the database at the same time as short online transactions. When the short transactions complete, the rollback segments they have used is up for grabs and can be overwritten. As soon as that area is overwritten, the long-running queries/transactions can no longer maintain a read consistent picture of the data, and they fail with an ORA-1555. It's a scheduling problem. Run batch and long-running jobs at off-hours.

Example Scenario:

1.      A long running Query (T1) is started.

2.      A quick update (T2) is performed and committed on a table that T1 won't require for another 20 minutes. When T2 is committed it's rollback segment blocks and extents are kept but marked as inactive.

3.      Another DML statement is issued (T3). Oracle assigns a rollback segment to T3 using a round robin algorithm. The assigned segment includes the same storage as the one previously used by T2. Thus it overwrites the inactive before-image of T2.

4.      T1 now comes to the point in the query where it needs the before-image of the data that was changed by T2.

5.      But T1 must read the before-image of the changed data records (for read consistency).

6.      T1 attempts to read the before-image left from T2 -- only to find that it has been overwritten by T3.

7.      T1 can no longer access the before-image of T2. T1 abends at this point.

8.      ORACLE then issues: ORA-1555: snapshot too old (rollback segment too small).