Many times like all Oracle DBAs have face with this problem sometimes is easy avoid sometimes you discover for example that table have LOBS and this LOBs dont use the UNDO tablespace and datbase UNDO_RETENTION parameter and have his own paramter and stores undo on data tablespace.
This problem can cause a lot of waste time because it is more likely to occur when you are running long queries so the return to process them again will be a big waste of time.
Sometime the error comes in production system where data volume difference between test and production environments, the obvious but very important solution to avoid this error is to have a real test environment with same data volume. Example: diferent volumen same query take lot of hours (no index or any other bad design) if exceed time set in undo_retention parameter error ORA-01555 ocurr.
This error is documented in oracle documentation as:
ORA-01555 snapshot too old: rollback segment number [string] with name “[string]” too small
Cause: Rollback records needed by a reader for consistent read are overwritten by other writers.
Action: Use larger rollback segments.
* How works oracle rollback segments? Oracle multi-versioning system.
When a query starts and a second query make a modification in data, queried by first query oracle will preserve a copy of this data before the modification happen in a place called rollback segment. Oracle uses this rollback segment to reconstruct the read-consistent snapshot of the data. The data provided by first query is reconstructed from rollback segment and presented as it was when the first query runs.
Consequently when you (or other session) are actively updating information you are reading through cursor or through sql query, oracle will be actively using its rollback segments in order to store those modifications aiming to provide you with a data read consistency. When oracle is not anymore able to restore a consistent data then you will receive the famous ORA-1555 rollback segment snapshot too old.
In summary this error is raised during a select process. Each update, done on your data during your process by your process or by another one, Oracle will log the “before update” image in it’s rollback segment. When your process is slow, and when your data are beeing heavily updated oracle might not be able to have enough space to guarantie the consistency of the “before image” drops ORA-01555.
Rollback segmentes are stored in undo tablespace and there a paramter that control retenetion time of data in undo
UNDO_RETENTION, bigger the value bigger space need to store rollback segments.
* Tips to avoid ORA-01555:
– Not commit accross fetch, commit only once for the whole process. Do not commit inside the loop
but if process runs in several hours comit outside loop allways but from time yto time to avoid error.
– Increase size of rollback segment
– Tune you query to make faster.
– Make your transaction (or job) restartable so that when an ora-01555 error occurs you can re-run your job
– Schedule your job in time where there is less concurrency