Re: Snapshot too old error with LOB Data

From: ddf <oratune_at_msn.com>
Date: Thu, 12 Apr 2012 17:45:00 -0700 (PDT)
Message-ID: <aff2b914-5064-4368-b104-9c528c2e621a_at_h20g2000yqd.googlegroups.com>

On Apr 11, 7:05 pm, zigzag..._at_yahoo.com wrote:
> I am on Oracle 10.2.0.4 on HP UNIX.
> 1.      I have a table with CLOB column:
> desc pr_text_data;
>  Name                                      Null?    Type
>  ----------------------------------------- -------- ----------------------------
>  ID                                        NOT NULL NUMBER(12)
>  PR_ID                                     NOT NULL NUMBER(12)
>  ADDITIONAL_ID                             NOT NULL NUMBER(12)
>  TEXT                                               CLOB
>  DATE_UPDATED                              NOT NULL DATE
> 2.      It has following lob  parameters:
> select pctversion, retention, segment_name, tablespace_name  from user_lobs where table_name = 'PR_TEXT_DATA'
> SQL> /
>                 86400
> SYS_LOB0000352143C00004$$
> TRACKWISE_DATA
> So you see retention  is set to 24 hours. Table was initially created with default pctversion of  10 and later I altered it to use retention which come from undo_retention) based on some Google articles.
> 3.      select SEGMENT_NAME, EXTENTS, MAX_EXTENTS from user segments where segment_name = 'PR_ADDTL_DATA';
> PR_ADDTL_DATA
>         99  2147483645
> So table can grow to unlimited extents.
> 4.      There is plenty  of free space variable in TRACKWISE_DATA in which table resides.
> 5.      Table has some text indexes on it. When I run a query, it gives me following snapshot error. I do not know how to fix it because lob retention parameter is 24 hours, so it should not overwrite old images for 24 hours, it has plenty of space in TRACKWISE_DATA as well as undotbs1, even though undotbs1 is not used for CLOB’s..
>
> ORA-29903: error in executing ODCIIndexFetch() routine
> ORA-20000: Oracle Text error:
> ORA-01555: snapshot too old: rollback segment number  with name "" too small
> ORA-22924: snapshot too old
> 29903. 00000 -  "error in executing ODCIIndexFetch() routine"
> *Cause:    The execution of ODCIIndexFetch routine caused an error.
> *Action:   Examine the error messages produced by the indextype code and
>            Take appropriate action.

Before images of LOB data are stored in the segment itself provided that MAXEXTENTS has not been reached or the tablespace containing the LOB segments has not filled to capacity. Additionally Oracle keeps PCTVERSION of the storage allocated for LOBs for before images; if PCTVERSION is set to too low of a value older images will be overwritten and, you guessed it, an ORA-01555 error appears. The PCTVERSION setting reserves that percentage of the total number of chunks of LOB data which have been allocated during the update process. Let's say you have 400 chunks already allocated, PCTVERSION is 5 and you need to update 33 chunks. 33 additional chunks are allocated for the before images of that data. This transaction succeeds and commits, freeing those 33 chunks for the next update. But, wait, PCTVERSION is set to 5 so 5% of 400 (20) of those chunks can't be touched to preserve the before images. This leaves 13 chunks of the previous allocation available for the next update transaction. If the next update affects 20 chunks then 7 additional chunks need to be allocated to complete that request. For an active table with LOB data this could continue on and on, where Oracle reuses some chunks from a prior update but also allocates additional chunks, until MAXEXTENTS is reached, the tablespace is filled or the series of update transactions comes to an end. A long-running select against that data will probably need those now-overwritten before images and, because they're no longer available both an ORA-01555 ("snapshot too old, rollback segment too small") and an ORA-22924 ("snapshot too old", relating to the PCTVERSION setting) are raised. This occurrence of an ORA-01555 isn't corrected using the methods listed previously, it's corrected by increasing the PCTVERSION for the LOB segment in question:

SQL> alter table lobex modify lob (isalob) (pctversion 10);

Table altered.

SQL> Verifying the setting has been changed:

SQL> select table_name, column_name, pctversion   2 from user_lobs
  3 where table_name = 'LOBEX'
  4 /

TABLE_NAME                     COLUMN_NAME          PCTVERSION
------------------------------ -------------------- ----------
LOBEX                          ISALOB                       10

SQL> So what if you query USER_LOBS and find that PCTVERSION is NULL? The LOB has been created with the RETENTION property set which retains the old versions of the LOB data for a period of time, and you'll see the RETENTION column of the USER_LOBS view populated with the current UNDO_RETENTION value:

SQL> select table_name, column_name, pctversion, retention   2 from user_lobs
  3 where pctversion is null
  4 /

TABLE_NAME                     COLUMN_NAME          PCTVERSION

RETENTION

------------------------------ -------------------- ----------
----------
LOBEX2                         ISALOB

900

SQL> Should this be the case the solution to curing the ORA-01555 is back to the usual remedy of increasing the UNDO_RETENTION, with the added steps of converting the existing LOBS to PCTVERSION and then back to using the UNDO_RETENTION:

SQL> alter system set undo_retention=28800;

System altered.

SQL> alter table lobex modify lob (isalob) (pctversion 10);

Table altered.

SQL> alter table lobex modify lob (isalob) (retention);

Table altered.

SQL> Without the conversion to PCTVERSION and back the increase in the UNOD_RETENTION is not passed through to the old LOB segments and the problem remains. You can, of course, leave the given LOB segment set to pctversion and manage it that way. The choice is yours, however managing heavily updated LOB data may be easier with RETENTION set rather than configuring the PCTVERSION, even with having to convert it to pctversion for a moment then reverting it back to retention.

David Fitzjarrell Received on Thu Apr 12 2012 - 19:45:00 CDT