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

Last updated on JUNE 02, 2023

Applies to:

Symptoms

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

RETENTION

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:
.
CREATE TABLE ContainsLOB_tab (n NUMBER, c CLOB)
lob (c) STORE AS SEGNAME (TABLESPACE lobtbs1 CHUNK 4096
RETENTION
NOCACHE LOGGING
STORAGE (MAXEXTENTS 5)
);

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
UNDO_RETENTION parameter.

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

Changes

Cause

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