Home Oracle DB Data Pump Export Fails With ORA-31693 ORA-02354 and ORA-01555 Errors And No...

Data Pump Export Fails With ORA-31693 ORA-02354 and ORA-01555 Errors And No LOB Corruption


While exporting a LOB table, we often face issue of undo_retention as follows.

 ORA-31693: Table data object ""."" 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 <segment_number> with name "" too small

Make sure to check the LOB is not corrupted by following the steps from Note:452341.1 or Note:787004.1 before following the below steps. If LOB is corrupted(logical corruption) you have to re-create the table.


The issue occurs because of incorrect setting of LOB table.

We can verify the same using following command.

SQL> show parameter undo;
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL> select max(maxquerylen) from v$undostat;

SQL> select retention from dba_lobs where owner='<SCHEMA_NAME>' and table_name='<TABLE_NAME>';
We see the retention comes back showing 900 seconds (15 minutes) which is the same as the current UNDO_RETENTION, but the maxquery length is 16331 seconds.
When the LOB was created, the actual setting for RETENTION was defined by the current setting for UNDO_RETENTION.
This time is not long enough.


Set a large enough value for undo_retention parameter using below steps.

  1. set the UNDO_RETENTION to 16500 using following command.
SQL>ALTER SYSTEM SET UNDO_RETENTION = 16500 scope=both sid='*';
Note: For versions =<, an instance restart is required because LOB retention does not change when undo_retention is changed. See further details from Document 563470.1 which shows that retention is not changed until after a restart.

2. Modify the LOB retention to become greater than the undersized retention parameter following the steps from Note:563470.1

SQL> alter table <SCHEMA_NAME>.<TABLE_NAME> modify lob(<LOB_COLUMN_NAME>) (pctversion 5);
Table altered.
SQL> alter table <SCHEMA_NAME>.<TABLE_NAME> modify lob(<LOB_COLUMN_NAME>) (retention);
Table altered.

3. Query the lob retention again to verify that the change has taken hold:

SQL> select retention from dba_lobs where owner='<SCHEMA_NAME>’ and table_name='<TABLE_NAME>’;


4. Perform the datapump export again.