Snapshot too old error with LOB Data
From: <zigzagdna_at_yahoo.com>
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 10.2.0.4 on HP UNIX.
1. I have a table with CLOB column:
desc pr_text_data;
select pctversion, retention, segment_name, tablespace_name from user_lobs where table_name = 'PR_TEXT_DATA' SQL> /
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.
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
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 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. Received on Wed Apr 11 2012 - 20:05:33 CDT