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

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

1 thought on “Avoid Snapshot too old error for LOB Segments by RETENTION or PCTVERSION

  1. Pingback: Smart way of Technology

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.