The Oracle (tm) Users' Co-Operative FAQ
I often get "ORA-01555 Snapshot too old" when running reports. What is this and how do I stop it happening ?
| Author's name: Jonathan Lewis Author's Email: Jonathan@jlcomp.demon.co.uk |
Date written: 26th July 2001 Oracle version(s): 7.3 - 8.1.7.0 |
| At random intervals, some of long running reports (or other types of lengthy batch processing, such as an export or very large update) fail for no apparent reason with an Oracle error 'ORA-01555 snapshot too old'. What causes this, and can it be fixed. |
The problem revolves around the work that Oracle does to ensure read consistency, and is basically due to a conflict of interest between multiple short transactions and occasional long-running reports/transactions. It is desirable for reasons of minimising I/O to keep rollback segments as small as possible; however a job which needs to see a consistent view of the database for a long period of time requires all the changes made by all the other processes on the database to be reversible - which may require a very large amount of rollback.
Rather than answer this question in detail though, I will refer you to a couple of the more thorough discussions of this issue that have been published on the web for some time.
Interestingly, Oracle verions 9 introduces the 'system managed undo tablespace (SMUT)' which allows you to define the time-period that must elapse before a piece of rollback data may be overwritten - thus allowing you to forget about calculating the necessary sizes of rollback segments needed to avoid 1555's, and only requiring you to know how long the task is likely to take.
Further reading: An article by David Wotton's on why the problem happens, An article by Steve Adams describing an ingenious mechanism for circumventing the issue when all else fails.
