Question: I have an export expdp job failing
with an ORA-01555 error:
ORA-31693: Table data
object "BAXXDB"."MS_DATA" failed to load/unload and
is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01555: snapshot too old: rollback segment
number 20 with name "_SYSSMU20_4037596720$" too small
All of the other tables (before and after) exported fine,
it is just this one table. How do I fix this
error in expdp?
Answer: There are several
solutions to this ORA-01555 problem.
-
Re-schedule long-running queries when
the system has less DML load.
-
Increasing
the size of your rollback segment (undo) size. The
ORA-01555 snapshot too old also relates to your setting
for
automatic undo retention.
-
Increase the size of
your undo_retention parameter. Automatic
undo management depends upon the
undo_retention parameter, which defines
how long Oracle should try to keep committed
transactions in UNDO segments. However, the
undo_retention parameter is only a
suggestion.
-
You must also have an
UNDO tablespace that’s large enough to handle the amount
of UNDO you will be generating/holding, or you will get
"ORA-01555: Snapshot too old, rollback segment too
small" errors.
-
If a table
has any columns defined a LOB? If so see MOSC Note:
452341.1. You can offload CLOB tables using
alternative techniques.
|
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|