RE: Specifying "RETENTION" in 9iR2 LOB segment still uses PCTVERSION?
- From: "Jesse, Rich" <Rich.Jesse@xxxxxx>
- To: "fairlie rego" <fairlie_r@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 11 Jun 2006 14:05:38 -0500
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@xxxxxxxxx]
Sent: Sat 06/10/2006 05:59
To: Jesse, Rich; oracle-l@xxxxxxxxxxxxx
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@xxxxxx> 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
--
//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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
