Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

Thursday 20 November 2014

ORA-01555 snapshot too old

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 reducing 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 is 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.

No comments:

Post a Comment