My Oracle Support Banner

Lob retention not changing when undo_retention is changed (Doc ID 563470.1)

Last updated on NOVEMBER 14, 2019

Applies to:

Oracle Database - Enterprise Edition - Version to [Release 8.1.7 to 10.2]
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Information in this document applies to any platform.
Checked for relevance on 09-Jul-2012


Disclaimer: this note illustrates a "problem"/behavior of setting retention in 10g (, and is not intended to explain how retention/pctversion works or more retention option in 11g/12c.

In 10g we have no control over the actual setting for RETENTION for lobs other than the current setting for UNDO_RETENTION at the time of the creation or modification of the LOB.

Stopping and starting the database will not modify the setting as it seems to be static.

It is assumed  that when UNDO_RETENTION is changed the lobs connected to that retention are also changed which is not the case . (this is incorrect)

If a lob is modified from RETENTION to PCTVERSION and back to RETENTION again then the lob retention is updated based on the current UNDO_RETENTION at the time that the LOB was altered / created to use RETENTION. 

 In 11g we allow users to specify retention for each LOB segment

Current documentation reads

Oracle� Database Application Developer's Guide - Large Objects
10g Release 2 (10.2)
Part Number B14249-01

4 LOBs in Tables


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

For example:

The RETENTION parameter is designed for use with Undo features of the database, such as Flashback Versions Query.When a LOB column has
the RETENTION property set, old versions of the LOB data are retained for the amount of time specified by the UNDO_RETENTION parameter.

Note the following with respect to the RETENTION parameter:

* Undo SQL is not enabled for LOB columns as it is with other datatypes. You must set the RETENTION property on a LOB column to use
Undo SQL on LOB data.

* You cannot set the value of the RETENTION parameter explicitly. The amount of time for retention of LOB versions in determined by the

* Usage of the RETENTION parameter is only supported in Automatic Undo Management mode. You must configure your tablefor use with
Automatic Undo Management before you can set RETENTION on a LOB column.

* The LOB storage clause can specify RETENTION or PCTVERSION, but not both.


To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.