Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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:
1 2 3 4 5 6 7 | SQL> show parameter undo; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 900 undo_tablespace |
Finding the longest query’s completed time:
1 2 3 4 5 | SQL> select max(maxquerylen) from v$undostat; MAX(MAXQUERYLEN) ---------------- 15429 |
Identify the lob retention value of the table that is causing the problem:
1 2 3 4 5 | SQL> select retention from dba_lobs where owner='USER' and table_name='TABLE'; RETENTION ---------- 900 |
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.
1 | SQL>ALTER SYSTEM SET UNDO_RETENTION = 15500 scope=both sid='*'; |
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.
1 2 3 4 5 | SQL> alter table USER.TABLE modify lob(LOB_COLUMN_NAME) (pctversion 5); Table altered. SQL> alter table USER.TABLE modify lob(LOB_COLUMN_NAME) (retention); Table altered. |
When we query the current lob retention value of the table, it must be the same as undo_retention.
1 2 3 4 5 | SQL> select retention from dba_lobs where owner='USER' and table_name='TABLE'; RETENTION ---------- 15500 |
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.