Snapshot too old error with LOB Data

From: <>
Date: Wed, 11 Apr 2012 18:05:33 -0700 (PDT)
Message-ID: <31008015.311.1334192733814.JavaMail.geo-discussion-forums_at_ynjn4>

I am on Oracle 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> /

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. Received on Wed Apr 11 2012 - 20:05:33 CDT

Original text of this message