ORA-22924: snapshot too old

We have few pet projects along with our main stream development project. Yesterday we got an error from one of the projects which loads huge data and perform biometric search operation. The investigation result requires modification in database.

I instantly checked the undo tablespace whether it was undersized or not. It was set to "auto extend on" as expected. Then I checked out the undo retention parameter-

SQL> show parameter undo_retention

It was set to default 900. I was thinking to modify it but was not so sure as oracle supporting documents were not available at hand.

Later, when I got Internet access, found that the error comes for the LOB segment undo problem. When data is modified, oracle keeps a version of the data in undo segment for read consistency.

I had two options-

1. Increase PCTVERSION in LOB table columns
2. Increase RETENTION in LOB table columns

1. PCTVERSION is the percentage of all used LOB data space that can be occupied by old versions of LOB data pages. As soon as old versions of LOB data pages start to occupy more than the PCTVERSION amount of used LOB space, Oracle Database tries to reclaim the old versions and reuse them. In other words, PCTVERSION is the percent of used LOB data blocks that is available for versioning old LOB data.

PCTVERSION has a default of 10 (%), a minimum of 0, and a maximum of 100. Setting PCTVERSION to twice the default value allows more free pages to be used for old versions of data pages. Because large queries may require consistent reads of LOB columns, it may be useful to retain old versions of LOB pages. In this case, LOB storage may grow because the database will not reuse free pages aggressively.

2. As an alternative to the PCTVERSION parameter, one can specify the RETENTION in CREATE TABLE or ALTER TABLE statement. Doing so, configures the LOB column to store old versions of LOB data for a parameter in the LOB storage clause of the period of time, rather than using a percentage of the table space.

I have planned to go with option 2 -

ALTER SYSTEM SET UNDO_RETENTION=2700 scope=both;
ALTER TABLE TABLE_NAME MODIFY LOB (COLUMN_NAME RETENTION);

The LOB RETENTION will take value from UNDO_RETENTION parameter.

Summary -
Cause: The version of the LOB value needed for the consistent read was already overwritten by another writer.
Action: Use a larger version pool/retention time.

One very important article of Tom Kyte -
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:275215756923