Re: Specifying "RETENTION" in 9iR2 LOB segment still uses PCTVERSION?
- From: fairlie rego <fairlie_r@xxxxxxxxx>
- To: Rich.Jesse@xxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Sat, 10 Jun 2006 03:59:33 -0700 (PDT)
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
Other related posts:
