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’.
