Hey all,

According to a LOB perftuning paper from Oracle, specifying RETENTION is
preferred over PCTVERSION for creating LOBs when using automatic UNDO.
In 9.2.0.5.0, a dev here has created a table whose subset of columns is
this:

CREATE TABLE CALL_INFORMATION
(
S_CALL_ID         VARCHAR2(20)          NULL,
S_CUSTOMERNO       VARCHAR2(6)           NULL,
S_MACHINE_NO       VARCHAR2(20)          NULL,
S_CALL_DESCRIPTION   CLOB               NULL
)
TABLESPACE QT_SMALL
LOB (S_CALL_DESCRIPTION) STORE AS
   ( TABLESPACE QT_SMALL
    ENABLE    STORAGE IN ROW
    CHUNK     8192
    RETENTION
    CACHE
   )
/

Notice the "RETENTION" keyword in there? But when the DDL is grabbed:

SELECT dbms_metadata.get_ddl('TABLE', 'CALL_INFORMATION')
FROM dual;

The RETENTION is ignored and PCTVERSION is used:

...LOB ("S_CALL_DESCRIPTION") STORE AS (
TABLESPACE "QT_SMALL" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
CACHE...

Huh? DBA_LOBS shows both PCTVERSION of 10 and a RETENTION equal to the
current value of the UNDO_RETENTION parameter. I even tested:

ALTER TABLE CALL_INFORMATION modify lob(S_CALL_DESCRIPTION) (retention);

...to no avail. Is this a bug? I altered the running system's
UNDO_RETENTION and noticed that DBMS_LOB's PCTVERSION still remained at
10 while the RETENTION correctly adjusted to the new value.

I couldn't find anything about this on MetaLink and Google searches were
too generic to be of use.

Thoughts?

Rich
--
http://www.freelists.org/webpage/oracle-l