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

From: Dan Halbert <halbert_at_bbn.com>
Date: Fri, 26 Mar 1999 02:24:04 GMT
Message-ID: <halbert.922413636@franklin>

This is Oracle 8.0.5.0.0 on Solaris 2.6.

I'm intermittently getting the following set of errors when attempting to read from a CLOB during a read-only transaction, and can't seem to get around this problem. Any advice would be appreciated. Have any of you experienced this problem?

Error:ORA-01555: snapshot too old: rollback segment number with name "" too sm\ all

ORA-22924: snapshot too old
ORA-06512: at "SYS.DBMS_LOB", line 441
ORA-06512: at "ME.MY_PKG", line 53   [this is a call to DBMS_LOB.INSTR()]

This error is described in an Oracle Note:
	http://support.oracle.com/cgi-bin/notes/og.pl?note:66431.1

Note that this error is misleading: rollback segments aren't actually involved. Instead, for LOB's, Oracle keeps outdated LOB values in the tablespace you've chosen for LOB data storage. The error above implies that Oracle has recycled the data pages the outdated LOB value was on, so the values are no longer available (if I understand correctly).

The value of the PCTVERSION parameter says what percent of the outdated LOB data pages will be kept. This is explained in detail in the Application Developer's Guide and in the Note mentioned above.

The default value of PCTVERSION is 10%. I've tried 20%, and then 100% (!), to no avail; we still get the above error intermittently.

The table in question has nearly all of its rows deleted and new rows inserted every fifteen minutes. There are a few hundred rows in the table. Thus there is almost complete turnover of the table every 15 minutes or so. (A few rows last much longer.)

Another part of the application queries this table with a read-only transaction that may last for many minutes (overlapping with the table update mentioned above). There may be a few (now less than four) such queries active simultaneously, started at random intervals.

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.

Here is the table definition. Blob1 is always NULL at present. It is the Text column that's causing the errors: the CLOB's in it are mostly under 4000 chars in length, with a only few over 4000 chars. Note that all the LOB storage parameters are defaulted except for PCTVERSION. This means for instance that ENABLE STORAGE IN ROW is true, and nearly all LOBs will be stored in the table rows anyway.

create table T (

  ID            integer,
  Blob1         blob,
  Text          clob,
  Chars         varchar2(2000),
  Timestamp     date default Sysdate,

constraint T_PK primary key(ID)
)
lob(Text) store as PCTVERSION(100);

Increase space available for old copies of lobs.

Sorry for being longwinded. There's a lot of background to this problem.

Thanks for any help,
Dan Halbert Received on Thu Mar 25 1999 - 20:24:04 CST