Re: Lob Corruption

From: Randolf Geist <info_at_sqltools-plusplus.org>
Date: Thu, 23 Feb 2012 01:00:59 -0800 (PST)
Message-ID: <f903aec5-6e62-4484-82d4-6f805c0f3813_at_w5g2000vbv.googlegroups.com>

On 23 Feb., 03:26, zigzagdna <zigzag..._at_yahoo.com> wrote:
> I am running following PL/SQL code   (see eblow) from Oracle to
> identify LOB corruption because I am getting
> ORA-1555 during exports on table containing BLOB, CLOB. I want to
> eliminate whether this is because of
> LOB corruption. When I run the script, I get following “errors”; but
> there is no way I can make those
> “errors” go away; because table does not have rows with those rowid’s.
> Appreciate your expert insight,

Is it possible that you have a problem with read consistency? Is the table PR_ADDTL_DATA subject to changes while you're executing this script? If yes, then the query executed within the loop will see the data as of a different time stamp than the outer driving query by default. Hence rows that will be returned by the driving query might have been deleted in the meanwhile.

If that's the case, a simple SET TRANSACTION ISOLATION LEVEL SERIALIZABLE (or via ALTER SESSION SET ISOLATION_LEVEL) or SET TRANSACTION READ ONLY may solve this problem - provided that you remove the COMMIT from the exception handler, that is.

Hope this helps,
Randolf Received on Thu Feb 23 2012 - 03:00:59 CST