Datapump export job failed with ORA-01555: snapshot too old: rollback segment number x with name “_SYSSMU8$” too small

The ORA-1555 errors can happen when a query is unable to access enough undo to build a copy of the data when the query started.
Committed “versions” of blocks are maintained along with newer uncommitted “versions” of those blocks so that queries can access data as it existed in the database at the time of the query. These are referred to as “consistent read” blocks and are maintained using Oracle undo management.

Error logs:

ORA-31693: Table data object "RPROD"."AOUP_DAILY_STBACT_SUMM_DET" 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 8 with name "_SYSSMU8$" too small

As a part of the solution, set undo_retention to a higher value. also, take care of the size of undo tablespace, it should be large enough.

SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ---------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
 
SQL> ALTER SYSTEM SET UNDO_RETENTION = 1800 scope=both;
System altered.
 
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ---------------
undo_management string AUTO
undo_retention integer 1800
undo_tablespace string UNDOTBS1

Export was successful after above changes.

Leave a Reply