L’explication se trouve dans la séquence de traitement d’Oracle présente notamment dans la note oracle 269814.1 “ORA-01555 Using Automatic Undo Management – Causes and Solutions”:.
. Database export is failing due to Snapshot too old. Datapump export failed with ORA-01555 on table with LOB ERROR: RA-31693: Table data object "OWNER. We have sufficient space on default undo tablespace. The default is 300 seconds (5 minutes), and you can set this parameter to guarantee that Oracle keeps undo logs for extended periods of time. If you can't change your "retention policy" (aka UNDO_RETENTION), you should search for other ways to improve your expdp performance. DISCLAIMER This document in any form, software or printed matter, contains proprietary information that is the exclusive property of Oracle.
. The table does not have any LOB columns. This is done by setting the UNDO_RETENTION parameter.
2 WHITE PAPER / Oracle Database Utilities Data Pump This document describes best practices for Oracle Data Pump Export and Import. Question: I am updating 1 million rows on Oracle 10g, and I run it as batch process, committing after each batch to avoid undo generation. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> show parameter undo NAME TYPE VALUE ----- ----- ----- undo_management string AUTO undo_retention integer 2400 undo_tablespace string UNDOTBS1 Expdp runs for 10 hours to backup 200GB database before it fails.
I am testing migration of 9i to 11g: While exporting a large schema which has one huge table in prod, i get ora-1555 snapshot too old. "DOC_GRP_BLOB" failed to load/unload and is being skipped due to error: ORA-023... WebLogic Server Patching 10.3.6 - SU Patch [GFWX] 27395085 Oracle ACE Steve Karam also has advice on avoiding the ORA-01555: Snapshot too old, rollback segment too small with UNDO sizing. Your access to and use of this confidential material is subject to the But in Oracle 10g I am told undo management is automatic and I do not need run the update as batch process. The default is 300 seconds (5 minutes), and you can set this parameter to guarantee that Oracle keeps undo logs for extended periods of time. Souvent après une erreur “ORA-01555“, le réflexe est d’agrandir la valeur du paramètre UNDO_RETENTION.Mais cela ne fonctionne pas vraiment .
Increasing the UNDO_RETENTION parameter did not help. You can try one (or more) of … When the same export runs from another server, it shows the elapsed time for the same table as around 20 minutes.