exporting LOBS - PCTVERSION & RETENTION

Owned by Clive Otten (Unlicensed)
Jan 21, 2020
2 min read

Problem Description

Export issue may be due to PCTVERSION table setting.

From a Tom Kyte article (@https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1100233456522) 

If a session is attempting to use an OLD version of a LOB and that version gets overwritten (because PCTVERSION is too small) then the user will  typically see the errors:

ORA-01555: snapshot too old: rollback segment number with name "" too small
ORA-22924: snapshot too old

PCTVERSION can prevent OLD pages being used and force the segment to extend instead.

Solution

This can happen if there is a high amount of concurrent updates as well as reads of the LOB column, then you need to choose a high value for PCTVERSION either when creating a table with LOBS or alter the retention.

PCTVERSION is the percent of LOB storage space kept for old versions of LOB pages to  maintain read consistency for reads started before an update. The default value is 10  percent.

You use the "store as" clause to set this

CREATE TABLE CLOB_TABLE ("X" CLOB) 
PCTFREE 10 PCTUSED 40 
INITRANS 1 MAXTRANS 255 
LOGGING STORAGE(INITIAL 524288) 
TABLESPACE "USERS" 
LOB ("X") STORE AS (TABLESPACE "USERS" ENABLE STORAGE IN ROW 
CHUNK 8192 
PCTVERSION 10
NOCACHE STORAGE(INITIAL 524288)) ;


PCTVERSION can be changed after creation using:
ALTER TABLE table_name MODIFY LOB (lobname) ( PCTVERSION n );

 

Additional Information on this topic

In the past, one would use the PCTVERSION storage parameter for their LOB segments to reserve a percentage of storage  space for read consistency of LOB segments.

In Oracle 11g, you can now use the RETENTION parameter. The RETENTION parameter will use the UNDO_RETENTION parameter for determining how long to keep LOB data for read-consistency purposes. 

But be advised that it does not use the Undo tablespace! The LOB segment’s tablespace is used for read-consistency purposes.

When you change the UNDO_RETENTION parameter, the LOB segment’s retention value is not modified.

If you query the RETENTION column of the DBA_LOBS view, you will notice the old UNDO_RETENTION value still remains  after you have modified the UNDO_RETENTION parameter. 

To change the LOB segment’s RETENTION value to match the new UNDO_RETENTION value, do the following:

ALTER TABLE table_name MODIFY LOB (lob_column) (PCTVERSION 20);

ALTER TABLE table_name MODIFY LOB (lob_column) (RETENTION);

By momentarily changing the LOB to use PCTVERSION and then back to RETENTION, the new value of UNDO_RETENTION will be used. 
You need to do this for all LOB segments that you intend to modify.