Lob retention not changing when undo_retention is changed
Last updated on OCTOBER 01, 2017
Applies to:Oracle Database - Enterprise Edition - Version 126.96.36.199 to 10.2.0.5 [Release 8.1.7 to 10.2]
Information in this document applies to any platform.
Checked for relevance on 09-Jul-2012
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.
CREATE TABLE ContainsLOB_tab (n NUMBER, c CLOB)
lob (c) STORE AS SEGNAME (TABLESPACE lobtbs1 CHUNK 4096
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
* 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.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms