Wikipedia

Search results

Avoid Snapshot too old error for LOB Segments by RETENTION or PCTVERSION

Read consistent are retained in the LOB segment with help of parameter RETENTION or PCTVERSION. You can increase the value of RETENTION or PCTVERSION attribute of the LOB column. A high value for RETENTION or PCTVERSION may be needed to avoid ‘snapshot too old’. RETENTION uses the same value as UNDO_RETENTION value.


Meaning:

RETENTION – time-based: this specifies how long older versions are to be retained.

PCTVERSION – space-based: this specifies what percentage of the LOB segment is to be used to hold older versions.


Identified which one is used RETENTION or PCTVERSION

col object_name for a24

col PCTVERS/RETENT for a25

select b.object_name, case bitand(a.flags,32) when 32 then 'RETENTION' when 0 then 'PCTVERSION' end "PCTVERS/RETENT" from sys.lob$ a, dba_objects b where a.obj# = b.object_id and b.owner='TEST' and object_name = 'TABLE_NAME';

Upto 11gR1 released,

The RETENTION attribute of the LOB segment will be equal to the UNDO_RETENTION parameter.

Hence we recommend to set UNDO_RETENTION to the maximum of the duration of the queries in the database.


Check the maxquerylen:

select max(maxquerylen) from v$UNDOSTAT;

Show parameter undo_retention

Increase the undo_retention

Alter system set undo_retention=value scope=both;


After 11gR1 released,

WE can set RETENTION of LOB Segments seperatly.

Check retention for LOB segments object like table:

col owner for a8

col table_name for a20

col segment_name for a28

select owner,table_name,segment_name,pctversion,retention from dba_lobs where table_name='Table_name';

Example:

col table_name for a20

col segment_name for a28

select owner,table_name, segment_name, pctversion, retention from dba_lobs where table_name in ('EMPLOYEE');

Increase the PCTVERSION attribute of the LOB segment


alter table tablename modify lob(lob column name) (pctversion 50);

Example:

alter table Employee modify lob(Employee_picture) (pctversion 50);


Note:

1. PCTVERSION=0: the space allocated for older versions of LOB data in LOBSEGMENTS can be reused by other transactions and can cause’snapshot too old?

2. PCTVERSION=100: the space allocated by older versions of LOB data can never be reused by other transactions. LOB data storage space is never reclaimed and it always increases.

3. Higher values of PCTVERSION will ensure the more space is allocated for the old versions of LOB data.

4. A high value for RETENTION or PCTVERSION may be needed to avoid ‘snapshot too old’.