Re: LOBs, PCTVERSION, and ORA-22924: snapshot too old

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 31 Mar 1999 11:35:35 +0100
Message-ID: <922876621.11922.0.nnrp-01.9e984b29@news.demon.co.uk>

I've had a little play with this, and the problem seems to be standard read-consistency.

I've created a table with a LOB column (trying both in-line enabled and disabled), and inserted one row.

This generates 3 segments:

    The table segment
    The LOB segment
    The LOB_INDEX segment

The LOB segment blocks appear to be controlled by a different read-consistency model from the other blocks in that there is no ITL section (which is reasonable since any one block should be allocated to one version of one LOB, and is not shared with multiple other LOBs). The LOB segment simply has a commit number on it.

The LOB_INDEX segment, however, has the normal ITL entries on it, and is therefore presumably subject to exactly the same read-consistency mechanisms of the rest of the database.

The Test:

Session 1 - read the LOB pointer from the one row in the table - go to sleep for 60 seconds, then try to read the lob content for that pointer.

Session 2 - whilst session 1 is sleeping, start inserting, deleting and commiting lots of rows to the database to run round all the available rollback, and guarantee that every block has been overwritten (total rollback blocks 128, total rollback blocks written ca. 1,600)

Result:

when session 1 wakes up and tries to read the LOB, it gets SNAPSHOT TOO OLD.

Follow-up

Without commiting the read-only transaction in session 1 try to dump the block from the table, the lob index, and the lob segment. The table and lob index fail with SNAPSHOT TOO OLD, the lob segment dumps, and shows that the actual loaded value is still stored.

Conclusion:

Your failure is occurring because either the table, or the lob index has an SCN that cannot be rolled back to - given your most recent description, I think it is more likely to be the LOB_INDEX that is causing the problem.

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk Received on Wed Mar 31 1999 - 04:35:35 CST