My Oracle Support Banner

ORA-1578 ORA-26040 in a LOB segment - Script to solve the errors (Doc ID 293515.1)

Last updated on AUGUST 03, 2020

Applies to:

Oracle Database - Standard Edition - Version 8.1.7.0 and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Information in this document applies to any platform.
***Checked for relevance on 10-Feb-2011***
***Checked for relevance on 3-Jul-2015***
***Checked for relevance on 29-Jul-2020***



Symptoms

Purpose:

The purpose of this article is to provide a script to update a lob column, that is referencing a lob block marked as corrupted due to NOLOGGING operations, with an empty lob.

It will avoid errors ORA-1578 / ORA-26040 when the lob column is accessed by a sql statement like a SELECT and a table export can be produced if needed.

Problem:

ORA-1578 and ORA-26040 are produced when reading a lob column in a table:


IMPORTANT

When ORA-26040 is not produced along with ORA-1578 the block is then corrupt by a different reason and Block Media recovery can be used to repair the corruption like RMAN BLOCKRECOVER (BMR).

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
 1. Query dba_extents to find out the lob segment name.
 2. Query dba_lobs to identify the table_name and lob column name:
 XMLTYPE
 3. Identify the table rowid's referencing the corrupted lob segment blocks by running the following plsql script:
 XMLTYPE
 4. Update the lob column with empty lob to avoid ORA-1578 and ORA-26040:
 XMLTYPE
 5. Observations
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.