ORA-1555: SNAPSHOT TOO OLD DUE TO CORRUPTED LOBS
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).
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
Platform : Oracle Linux Server 7.2
Database : Oracle database 11.2.0.4