When attempting to export tables that contain lobs, the following errors occur:

Usually, the ORA-01555 error is related to both the undo retention, and the undo guarantee parameters. Or when an undo tablespace is inadequately sized. But the cause of this error could be a LOB corruption as well.

To fix the ORA-01555 error when it relates to a LOB corruption you have to identify the corrupted rows and reinsert, or delete, or exclude, or empty them.

In this demo, I perform a table export where there is one corrupted row of LOBORA-01555 error.

[oracle@dbpilot ~] expdp \"/ as sysdba\" tables="ISUGDP"."TEXT_FOR_ECP" directory=DROPZONE dumpfile=table.dmp 
logfile=table.log

Export: Release 11.2.0.4.0 - Production on Mon Feb 5 10:48:14 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
Starting "SYS"."SYS_EXPORT_TABLE_01":  "sys/******** AS SYSDBA" tables=ISUGDP.TEXT_FOR_ECP directory=DROPZONE 
dumpfile=table.dmp logfile=table.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 6.108 GB
>>> DBMS_AW_EXP: SYS.AW$EXPRESS: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWMD: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWCREATE: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWCREATE10G: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWXML: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWREPORT: OLAP not enabled
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
>>> DBMS_AW_EXP: SYS.AW$EXPRESS: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWMD: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWCREATE: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWCREATE10G: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWXML: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWREPORT: OLAP not enabled
ORA-31693: Table data object "ISUGDP"."TEXT_FOR_ECP" 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  with name "" too small
ORA-22924: snapshot too old
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /oracle/oradumps/table.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at Mon Feb 5 10:49:01 2018 elapsed 0 00:00:45

For instance, my table export completed with the following error

The reason for this error is not related to UNDO at all. It happened because the "ISUGDP"."TEXT_FOR_ECP" table has a corrupted row containing LOB datatype.

COL COLUMN_NAME FOR A15
COL DATA_TYPE FOR A15

SELECT COLUMN_NAME, DATA_TYPE 
FROM   DBA_TAB_COLUMNS 
WHERE  OWNER='ISUGDP' AND TABLE_NAME='TEXT_FOR_ECP';

COLUMN_NAME     DATA_TYPE
--------------- ---------------
DOCUMENT_ID     NUMBER
USER_ID         NUMBER
USER_NAME       VARCHAR2
REQUEST_ID      NUMBER
CREATED         DATE
MODIFIED        DATE
IS_REPLICON     NUMBER
TEXT            CLOB                    <== This column

a. Create a new temporary table for storing all rowids of the corrupted rows

SQL> CREATE TABLE CORRUPTED_ROWS (CORRUPTED_ROWID ROWID, ERROR_NUMBER NUMBER);

Table created.

b. The following PL/SQL block identifies all corrupted rows in a table and adds them to the table created in step a. It might take a while as it scans the whole table.

SET TIMING ON
DECLARE 
   ERROR_1578  EXCEPTION;
   ERROR_1555  EXCEPTION;
   ERROR_22922 EXCEPTION;
   PRAGMA EXCEPTION_INIT(ERROR_1578,  -1578);
   PRAGMA EXCEPTION_INIT(ERROR_1555,  -1555);
   PRAGMA EXCEPTION_INIT(ERROR_22922, -22922);
   N NUMBER;
BEGIN
   FOR ROW IN (SELECT ROWID, TEXT FROM ISUGDP.TEXT_FOR_ECP)
   LOOP
      BEGIN
         N:=DBMS_LOB.INSTR(ROW.TEXT, HEXTORAW('889911'));
	  EXCEPTION
         WHEN ERROR_1578  THEN 
              INSERT INTO CORRUPTED_ROWS VALUES (ROW.ROWID, 1578);
			  COMMIT;
         WHEN ERROR_1555  THEN 
              INSERT INTO CORRUPTED_ROWS VALUES (ROW.ROWID, 1555);
			  COMMIT;
         WHEN ERROR_22922 THEN 
              INSERT INTO CORRUPTED_ROWS VALUES (ROW.ROWID, 22922);
			  COMMIT;
       END;
   END LOOP;
END;
/

Adjust a table name for your needs

c. When step b is completed, query the CORRUPTED_ROWS table to list all corrupted ROWIDs.

SQL> SELECT * FROM CORRUPTED_ROWS;

CORRUPTED_ROWID    ERROR_NUMBER
------------------ ------------
AAAWJ9AAGAAPJTWAAJ         1555

d. If you query rows found in step c you can easily confirm the existence of ORA-01555 error. The error arises when a row with a corrupted LOB is touched.

-- Querying corrupted rows excluding LOB column

SQL> SELECT DOCUMENT_ID FROM ISUGDP.TEXT_FOR_ECP WHERE ROWID IN (SELECT CORRUPTED_ROWID FROM CORRUPTED_ROWS);

DOCUMENT_ID
-----------
  700061427

-- Querying corrupted rows including LOB column

SQL> SELECT DOCUMENT_ID, TEXT FROM ISUGDP.TEXT_FOR_ECP WHERE ROWID IN (SELECT CORRUPTED_ROWID FROM CORRUPTED_ROWS);
ERROR:
ORA-01555: snapshot too old: rollback segment number  with name "" too small
ORA-22924: snapshot too old

Retry a table export excluding corrupted rows. It will complete successfully.

[oracle@dbpilot ~] expdp \"/ as sysdba\" tables="ISUGDP"."TEXT_FOR_ECP" directory=DROPZONE dumpfile=table.dmp 
logfile=table.log QUERY=\"WHERE rowid NOT IN \(SELECT CORRUPTED_ROWID FROM CORRUPTED_ROWS\)\"

Export: Release 11.2.0.4.0 - Production on Mon Feb 5 11:37:24 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
Starting "SYS"."SYS_EXPORT_TABLE_01":  "/******** AS SYSDBA" tables=ISUGDP.TEXT_FOR_ECP directory=DROPZONE 
dumpfile=table.dmp logfile=table.log QUERY="WHERE rowid NOT IN (SELECT CORRUPTED_ROWID FROM CORRUPTED_ROWS)"

Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 6.108 GB
>>> DBMS_AW_EXP: SYS.AW$EXPRESS: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWMD: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWCREATE: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWCREATE10G: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWXML: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWREPORT: OLAP not enabled
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
>>> DBMS_AW_EXP: SYS.AW$EXPRESS: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWMD: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWCREATE: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWCREATE10G: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWXML: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWREPORT: OLAP not enabled
. . exported "ISUGDP"."TEXT_FOR_ECP"                     4.489 GB 1368214 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /oracle/oradumps/table.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Mon Feb 5 11:38:05 2018 elapsed 0 00:00:40

LOBs and ORA-01555 troubleshooting (Doc ID 846079.1)
Export Fails With Errors ORA-2354 ORA-1555 ORA-22924 And How To Confirm LOB Segment Corruption Using Export Utility (Doc ID 833635.1)

Written At

05 FEB 201812:40

OEL Release

7.2 x64

Database Release

11.2.0.4