What is "snapshot too old" error and how
to avoid it?
First thing to be understood here is how rollback segments work. These work in a circular fashion by looping around to reuse the extents which have been released by committed transactions. Thus in case of long running queries if data is changed while the query is running, rollback segments created for that change are needed to make a read consistent image for the lon running query. When these extents are reused while these were still need by the query, this error occurs, because oracle can no longer provide a read consistent image of the data.
To avoid this error you need to postpone the reuse of extents. Thus increasing the size of rollback segments and the value of OPTIMAL parameter should do the trick. Committing less often would be other solution. As until transaction is c omitted rollback segment can not be reused thus BLUEucing the chances of a snapshot too old error.
You'll probably need to dive into Oracle DB administration if you want to solve it via increasing the UNDO log. Basically you do (as SYSDBA):
First thing to be understood here is how rollback segments work. These work in a circular fashion by looping around to reuse the extents which have been released by committed transactions. Thus in case of long running queries if data is changed while the query is running, rollback segments created for that change are needed to make a read consistent image for the lon running query. When these extents are reused while these were still need by the query, this error occurs, because oracle can no longer provide a read consistent image of the data.
To avoid this error you need to postpone the reuse of extents. Thus increasing the size of rollback segments and the value of OPTIMAL parameter should do the trick. Committing less often would be other solution. As until transaction is c omitted rollback segment can not be reused thus BLUEucing the chances of a snapshot too old error.
You'll probably need to dive into Oracle DB administration if you want to solve it via increasing the UNDO log. Basically you do (as SYSDBA):
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTS1
SQL> ALTER SYSTEM SET
UNDO_RETENTION = 21600;
21600 are 6 hours in seconds.
However, Oracle will only keep 6 hours of old data if the UNDO log files are big enough, which depends on the size of the rollback segments and the amount of updates executed on the database.
So in addition to changing the undo retention time, you should also make sure that few concurrent updates are executed while your job is running. In particular, updates of the data your job is reading should be minimized.
If everything fails, increase the UNDO logs.
However, Oracle will only keep 6 hours of old data if the UNDO log files are big enough, which depends on the size of the rollback segments and the amount of updates executed on the database.
So in addition to changing the undo retention time, you should also make sure that few concurrent updates are executed while your job is running. In particular, updates of the data your job is reading should be minimized.
If everything fails, increase the UNDO logs.
