ORA-31693 ORA-02354 ORA-01555 snapshot too old During Export backup via expdp

Hi,

I will explain how to solve ORA-31693 ORA-02354 ORA-01555 During Export backup error.

Details of error are as follows.

ORA-31693: Table data object "USER"."TABLE" 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 154 with name “_SYSSMU154_1434655980$” too small

 

To solve this problem, increase the undo_retention as follows.

 

SQL> show parameter undo

NAME              TYPE               VALUE
------------------------------------ ----------- 
undo_management   string             AUTO
undo_retention    integer            900
undo_tablespace

 

SQL> alter system set undo_retention=90000 scope=both sid='*';

System altered.

 

 

 

Query the retention of table as follows.

SQL> select retention from dba_lobs where table_name='DEVRAWDATA';

RETENTION
----------
900

 

lob retention of the table are updated as follows.

SQL> alter table MSD.DEVRAWDATA modify lob(VALUE) (pctversion 5);

Table altered.

SQL> alter table MSD.DEVRAWDATA modify lob(VALUE) (retention);

Table altered.

SQL>
SQL> select retention from dba_lobs where table_name='DEVRAWDATA';

RETENTION
----------
900000

Now problem has been solved.

 

 

Do you want to learn Oracle Database for Beginners, then read the following articles.

https://ittutorial.org/oracle-database-19c-tutorials-for-beginners/