Print Friendly, PDF & Email

 
QUESTIONS

During schema(s) export or querying a table containing a clob column I recieve the following error

ORA-01555: snapshot too old: rollback segment number with name "" too small

 
SOLUTION

Usually ORA-01555 is related to undo retention and undo guarantee parameters. But there is another case when you’ll face the same error and increasing of undo parameters won’t help in this situation. It happens when a lob column has corrupted row(s).
To fix the issue, you can reinsert, delete, exclude or empty corrupted LOBs.

 
TESTCASE

Create a database directory for datapump export file

SQL> create or replace directory DROPZONE as '/oracle/oradumps';

Directory created.

Here I’m trying to export a table with corrupted LOB. The export completes with the ORA-01555 error

[oracle@dbpilot ~] expdp 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

The undo retention parameter is big enough and retention guarantee is set for the undo tablespace.
The reason why it happens the “ISUGDP”.”TEXT_FOR_ECP” table has a lob column with corrupted row

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

 

To find out all corrupted rows in the table

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

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

Table created.

b. Run the following PL/SQL block

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;
/

NOTE: Modify this piece of code for your needs

(SELECT ROWID, TEXT FROM ISUGDP.TEXT_FOR_ECP)

as

(SELECT ROWID, YOUR_LOB_COLUMN FROM YOUR_OWNER.YOUR_TABLE)

c. When step b is completed, query found corrupted rowid(s)

SQL> SELECT * FROM CORRUPTED_ROWS;

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

d. By querying found rows from step c, you can easily confirm existence of ORA-01555. Error arises when querying corrupted LOBs

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

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

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

With excluding of the corrupted row(s) it becomes possible to perform the new export 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

 
REFERENCES

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)

 
 

Version  : 12:41 05.02.2018
Platform : Oracle Linux Server 7.2
Database : Oracle database 11.2.0.4