Common Problems/Solutions
ORA-01555 Snapshot Too Old
- occurs when you have a long-running transaction that finds it no longer has a read-consistent version of the table or tables it is using - happens whenever rollback segments are written over by new transactions prematurely.
Avoid this problem by:
- Increase the size of your rollback segments and the size of your rollback segment extents.
- Schedule large jobs when no one else will be using the system and use SET TRANSACTION...USE ROLLBACK SEGMENT to assign a larger rollback segment to this trsnaction (can't lock a rollback segment for a specific user though).
- If you have multiple CPUs, use table prallelism to reduce transaction duration.
- In a large transaction, close cursors explicitly as soon as they are no longer needed. If you can reduce a large transaction to a series of smaller ones, you will be less likely to encounter this error. Try closing the cursor and committing after a specific number of rows are processed. Rollback segments aren't released until the cursor is closed in spite of how many commits occur.
- If the type of transaction you are using has a unrecoverable option (such as table and index creation), use it; this will not generate rollback.
- For large table deletions, use the truncate command because it doesn't use rollback.
