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

On Fri, 26 Mar 1999 02:24:04 GMT, halbert_at_bbn.com (Dan Halbert) wrote:

>
>I think it's the combination of the long read-only transaction
>spanning an almost-complete table turnover that's causing me problems,
>but I don't understand why Oracle won't keep lots of old datapages
>around given the high value of PCTVERSION, and get new datapages
>instead. The tablespace is nowhere near full: it's less than 2MB out
>of 100MB, and it isn't growing, so Oracle doesn't seem to be growing
>the LOB data segment.

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:

When a LOB is modified, a new version of the LOB page is made in order to support consistent read of prior versions of the LOB value. PCTVERSION is the percent of all used LOB data space that can be occupied by old versions of LOB data pages. As soon as old versions of LOB data pages start to occupy more than the PCTVERSION amount of used LOB space...

According to the sql reference, pctversion: is the maximum percentage of
overall LOB storage space used for
creating new versions of the LOB.
The default value is 10, meaning that
older versions of the LOB data are
not overwritten until 10% of the
overall LOB storage space is used.

It may be ( guessing ) that this mechanism is to provide read consistency within a blob rather than deletion of the blob. ( A guiding design principle at Oracle seems to be that no new feature will work quite as any reasonable person would expect it to - see row migration in partitioned tables ).

The answer would seem to be to lock the rows being read as they are read which should prevent updates by others (though I've never tried it with a blob) or to add a flag to the record to indicate that it has been processed by the read process and make the read process not read only. Received on Sat Mar 27 1999 - 09:36:05 CST