|
ORA-01555 : Snapshot Too Old in Oracle |
|
ORA-01555, Snapshot too old error in oracle indicates that something else other than your current process is creating the issue. Oracle typically creates a read consistent view of the table so that it looks the same at the start and end of a sql operation. ORA-01555 indicates something has overwritten the undo area that keeps the data to maintain such a view.
ORA-01555 : Snapshot Too Old
ORA-01555 Cause : Rollback records needed by a reader for consistent read are overwritten by other writers
ORA-01555 Action : If in automatic undo management mode, increase the setting of UNDO_RETENTION. Otherwise use a dedicated larger segment or switch to automatic undo management
ORA-01055 Notes :
- Avoid running discrete transactions while critical transactions are running.
- Schedule long running jobs and batch in non-critical hours so that the gets in oracle do not need to rollback changes made since the last snapshot. This also improves performance
- ORA-01555 can also be avoided through design changes that would rewrite the code as a series of steps that can be restarted without having to run the entire process
- ORA-01555 can also be eliminated in a manual Undo situation through utilization of a large optimal value on rollback segments
- ORA-01555 can be avoided by eliminating the fetch on a cursor that was opened prior to the previous commit.
- Large database blocksize also helps by maximizing the number of slots in the rollback segment transaction tables there by delaying slot reuse and eliminating the ORA-01555
- Increasing undo_retention may help in some cases depending on the
real cause. Check v$undostat, it may have
information in there if this the operation causing the ORA-01555 is still ongoing.
|
