"ORA-01555: Snapshot too old" on Exports
There are countless pages on the web that seem to deal with "ORA-01555: snapshot too old" errors, yet on long exports you may still encounter these. In this topic are the essential steps to cure "snapshot too old" errors. This assumes you are using a modern undo tablespace configuration with AUTOEXTEND ON and UNDO_MANAGEMENT set to AUTO. Having your UNDO tablespace created as a Big File Tablespace (BFTS) can't hurt too.
- Export during the most quiescent time possible for the data, i.e. during non-production hours.
- Ensure schema\tables being exported are optimized.
This includes: OS disk fragmentation, table segments, indexes and stats.
These can influence how long an export takes. - Increase the size of your UNDO tablespace.
10gb to 25gb is common in some environments. - Increase the size of your UNDO_RETENTION.
The default is 900 (seconds). Common values in this instance range from 21600 (6 hours) to 43200 (12 hours).
Experiment with this value and change as required for your environment:
Single Instance\Database ALTER SYSTEM SET UNDO_RETENTION=21600 SCOPE=both;
All RAC Instances ALTER SYSTEM SET UNDO_RETENTION=21600 SID='*' SCOPE=both;