My Oracle Support Banner

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

Last updated on AUGUST 04, 2018

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

To view full details, 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 a vibrant support community of peers and Oracle experts.