Data Pump Export process ends with ORA-31693, ORA-02354 and ORA-01555 errors

When taking a backup with Expdp, it can be terminated with ORA-31693, ORA-02354 and ORA-01555 errors when it comes to a table with a lob column. There are two main reasons for this.

  • You can have a corruption in the lob column in the related table
  • The lob retention value of the Related Table is not as it should be

The expdp process terminates 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

  • In the first case, the relevant corrections need to be corrected. Then it will be completed without errors.
  • In the second case, the retention value of the lob column in the corresponding table and the value of the system’s undo_retention should be increased sufficiently.

Find the current value of undo_retention:

Finding the longest query’s completed time:

Identify the lob retention value of the table that is causing the problem:

In the above example, the completed time of the longest query is 15429 seconds. The lob retention value of the table, and the value of undo_retention is 900 seconds (15 minutes).

In this case, undo_retention should be increased and the lob retention value of the table should be equalized.

Change undo_retention:

The value of undo retention should be given more than the completion time of the longest query. Also make sure that there is enough undo tablespace for the value of undo_retention.

Change the lob retention of the table:

Undo retention value of the system and the lob retention value of the table are equalized with this change.

When we query the current lob retention value of the table, it must be the same as undo_retention.

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

One comment

  1. Hi,
    I’ve 3 tables for which the same error is occuring. But I don’t have any lob columns in 2 of those tables. There is varchar2 of size 255 bytes.

    Is it possible for those tables that it is happening because of the same issue.

Leave a Reply

Your email address will not be published. Required fields are marked *