How To Identify LOB Segment Use PCTVERSION Or RETENTION From Data Dictionary (Doc ID 422826.1)

Last updated on JUNE 20, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.3 to 11.2.0.3 [Release 11.2]
Oracle Database - Enterprise Edition - Version 9.0.1.4 to 9.2.0.8 [Release 9.0.1 to 9.2]
Information in this document applies to any platform.
Oracle Server Enterprise Edition - Version: 9.0.1.4 to 9.2.0.8
***Checked for relevance on 22-Mar-2016***



Goal

The Purpose of this Note Is To explain the behavior of PCTVERSION and RETENTION  in LOB Segments.

The Following Example shows the issue.

Create a LOB specifying PCTVERSION = 5 and create another one specifying RETENTION.
When query DBA_LOBS, would not expect to see default PCTVERSION = 10 when RETENTION was specified.

SQL> CREATE TABLE lobpctversion
(LOBLOC blob,id NUMBER)
LOB ( lobLoc ) STORE AS
(TABLESPACE users STORAGE (INITIAL 5k NEXT 5k PCTINCREASE 0) pctversion 5);

Table created.

SQL> CREATE TABLE lobretention
(LOBLOC blob,id NUMBER)
LOB ( lobLoc ) STORE AS
(TABLESPACE users STORAGE (INITIAL 5k NEXT 5k PCTINCREASE 0) retention);

Table created.

SQL> select table_name, segment_name, pctversion, retention
from dba_lobs where table_name in ('LOBPCTVERSION', 'LOBRETENTION');

TABLE_NAME SEGMENT_NAME PCTVERSION RETENTION
----------------- --------------------------- ---------- ---------
LOBPCTVERSION SYS_LOB0000096861C00001$$ 5 10800
LOBRETENTION SYS_LOB0000096864C00001$$ 10 10800




> />see that both PCTVERSION and RETENTION columns have values

As You can see, would expect table named LOBRETENTION to clearly show 0 for PCTVERSION since RETENTION was specified. Likewise, LOBPCTVERSION should show only PCTVERSION. So when query DBA_LOBS down the road would have no indication which is truly active.

Also if You Change the Storage Parameters the Dictionary does not change.

> />

SQL> alter table lobretention modify lob(lobLoc) (pctversion 5);

Table altered.


SQL> select table_name, segment_name, pctversion, retention
from dba_lobs where table_name = 'LOBRETENTION';

TABLE_NAME SEGMENT_NAME PCTVERSION RETENTION
----------------- --------------------------- ---------- ---------
LOBRETENTION SYS_LOB0000096864C00001$$ 5 10800



see the change in value under PCTVERSION, but both PCTVERSION and RETENTION columns are populated.

Solution

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