Snapshot too old error with LOB Data

[zigz...@yahoo.com]

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.

[onedbguru]

On Wednesday, April 11, 2012 9:05:33 PM UTC-4, zigz...@yahoo.com wrote:
> I am on Oracle 10.2.0.4 on HP UNIX.
> 1. I have a table with CLOB column:

> 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.

Did you not like the answer that was given in forums.oracle.com yesterday?

https://forums.oracle.com/forums/thread.jspa?threadID=2374381&tstart=0

[joel garry]

I didn't like the answer given yesterday. The doe snot typo cracked
me up.

jg
--
@home.com is bogus.
Debra Lilley, president of the UKOUG, said that the opportunity to
quiz Kyte was one "that does not come along often".
http://www.computerworlduk.com/news/infrastructure/3350626/oracle-database-guru-tom-kyte-answers-your-sql-questions/

[ddf]

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