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 the ORA-01555 error is related either to the undo retention and the undo guarantee parameters, or to an inadequate size of an undo tablespace.
But there is also another case when you’ll face the same error and modifying of undo parameters or even increasing an undo tablespace size won’t help you. It happens when a column of LOB datatype has corrupted row(s).

You have several options to fix the issue: reinsert, delete, exclude or empty corrupted LOBs.

 
TESTCASE

In this test case I’ll try to perform a table export where there is one corrupted row of LOB data type.

This directory will be used for a table dump

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

Directory created.

Here I’m starting an export of a table with corrupted row of LOB data type. And I expect it completes with the ORA-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

The undo retention parameter is big enough in my case and retention guarantee is also set for the UNDO tablespace. The UNDO tablespace size is big enough too.
The reason why it happens in the “ISUGDP”.”TEXT_FOR_ECP” table, which has corrupted row of 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

 

The next steps help you out 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 to get a list of ROWID(s) for all corrupted rows in the table. It would take a while as it reads 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;
/

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) from the table of step a.

SQL> SELECT * FROM CORRUPTED_ROWS;

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

d. Now if you query rows with ROWID(s) from step c you can easily confirm existence of ORA-01555. The error arises when a corrupted row is queried.

-- Without the LOB column (TEXT)

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

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

-- With the LOB column (TEXT)

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

Next, If you exclude all corrupted ROWID(s) from an export then it becomes possible to perform a new export successfully. Use the QUERY option of the datapump utility to exclude all unwanted rows.

[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