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

Thanks for you comments, Jonathan and Keith.

  "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> writes:
>Dan Halbert has a process that is deleting his rows,
>not changing the LOBS which are part of the row.
>Perhaps it is the attempt to roll back the row header
>deletion that is causing the snapshot problem rather
>than access to the blob per se.

Well the row still seems to be there, because the PL/SQL code before the error does a successful "SELECT ... INTO ..." to get the CLOB, and I even do a DBMS_LOB.GETLENGTH() on the CLOB before I hit the error, which happens when I do a DBMS_LOB.INSTR(). Perhaps the length is available even after the lob data has been recycled.

The error message explanation in the doc for ORA-22924 is very terse, but says something about the "version pool" (as opposed to rollback segments) being too small.

  keith boulton wrote in message <36fceb6e.16490872_at_195.147.246.90>...
>I know nothing about blobs, but the Oracle documentation talks about
>pctversion being used to retain old versions of lob data and then
>talks about nothing but updates:
>
>It may be ( guessing ) that this mechanism is to provide read
>consistency within a blob rather than deletion of the blob.

That is an interesting point. Yes, I am doing deletes and not updates. But the doc says that internal LOBs do participate in transactions and can be rolled back, etc., so I would have expected the old LOB data to be there.

I could work around with various means: locking the rows and/or marking them as available for deletion (as Keith suggested), caching them locally, managing my own storage with BFILE's, etc. Of course, one reason we chose to use a DB for this project was to have nice multi-user data consistency, and avoid our own storage management. Oh, well.

Oracle has asked me to create a test case, and I will try to do that. I'm sure some developer in Oracle really knows the answer here, but I haven't gotten to that person yet.

Thanks,
Dan Received on Sun Mar 28 1999 - 12:46:20 CST