Specifying "RETENTION" in 9iR2 LOB segment still uses PCTVERSION?

From: Jesse, Rich <Rich.Jesse_at_qg.com>
Date: Fri, 9 Jun 2006 14:16:37 -0500
Message-ID: <FB5D3CCFCECC2948B5DCF4CABDBE6697A51D74@QTEX1.qg.com>

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

Received on Fri Jun 09 2006 - 14:16:37 CDT