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 18.104.22.168 to 10.2.0.5 [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
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.
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