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

From: Jesse, Rich <Rich.Jesse_at_qg.com>
Date: Sun, 11 Jun 2006 14:05:38 -0500
Message-ID: <FB5D3CCFCECC2948B5DCF4CABDBE66979063CE@QTEX1.qg.com>

Thanks, Fairlie. That BUG matches exactly. It's wierd that it's not a bigger deal, seeing that it doesn't seem possible to determine the correct storage of LOBs.

I'll file an SR with Oracle Support to see for sure.

BTW, what did you search keywords did you use to find that via MetaLink?

Rich

-----Original Message-----
From: fairlie rego [mailto:fairlie_r_at_yahoo.com] Sent: Sat 06/10/2006 05:59
To: Jesse, Rich; oracle-l_at_freelists.org
Subject: Re: Specifying "RETENTION" in 9iR2 LOB segment still uses PCTVERSION?

Hi Rich,

I am no LOB expert but I believe there is a related bug 4231990 which hasn't been updated since Mar 2005.

Regards,
Fairlie

"Jesse, Rich" <Rich.Jesse_at_qg.com> wrote: 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





          Fairlie Rego
Senior Oracle Consultant
  
  http://el-caro.blogspot.com/
   






 __________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 
--
http://www.freelists.org/webpage/oracle-l

Received on Sun Jun 11 2006 - 14:05:38 CDT