Error: "ORA-01555: snapshot too old" when performing a compare and repair on a large table.</p (4214667)

Compare fails with ORA-01555 as indicated in the event log on source or target as well as in the respective compare log (*desvr* log on source or *declt* log on target), the error shown here is from source:

The event log entries:

Error    2009-07-30 16:56:02.207278 29675 2 Compare server: Oracle Error 1555: ORA-01555: snapshot too old: rollback segment number 22 with name "NNNNNNN” too small.  [module deq]

Error    2009-07-30 16:56:02.291525 29675 2 Compare server: Error 1 in de_compare_server, see sbprod_desvr-29675-*.log  [module deq]

The *desvr* log entries:

249: Oracle Error 1555: ORA-01555: snapshot too old: rollback segment number XX  with name "NNNNNNN" too small

249: Error 1 calling de_batch_setup for pass 2 in sp_desvr's main.

249: Compare OWNER.TABLENAME failed. See error message above.

000: DEQ done reading.

000: Exiting....

The following workaround can be tried to resolve the issue:

• Run compare during periods of lesser activity.
• Run compare with “where” clause to reduce the number of rows being compared.
• Run compare without “repair” option and sync the target table manually using DML statements in the compare report. See Solution # SOL44594 for some pitfalls that can occur when following this approach.
• If using rollback segments, then create many large rollback segments, and make them all of the same size. Also, avoid the use of “OPTIMAL” parameter in rollback segments, or set it to a very high value if wanting to retain its usage.
• If using UNDO SEGMENTS rather than rollback segments, set the UNDO_RETENTION to a longer period of time than the compare is going to take. This may require trial and error approach.

Oracle uses rollback segments to reconstruct the read-consistent snapshot of the data. Whenever a transaction makes any changes, a snapshot of the record before the changes were made is copied to a rollback. Once a transaction is complete, its data is not deleted from the rollback segment. It remains there to service the queries and transactions that began executing before it was committed. This may cause problems with long queries because these blocks may be overwritten by other transactions, even though the separate long-running query against those blocks has not completed. Accordingly, this error can arise when a long-running query is being executed at the same time as data manipulation transactions. This causes ORA-01555 “snapshot too old”. Compare process runs SELECT on the source and target tables being compared to gather rows to be compared and can run into ORA-01555 when the blocks it selected are overwritten.