指定LOB参数RETENTION,PCTVERSION的一个小BUG

原文链接：

http://www.biti-feng.me/2011/01/30/%E6%8C%87%E5%AE%9Alob%E5%8F%82%E6%95%B0retentionpctversion%E7%9A%84%E4%B8%80%E4%B8%AA%E5%B0%8Fbug/

偶然发现，用dbms_metadata.get_ddl()抓取带LOB 的TABLE建表SQL的时候，出现一个小BUG。
版本是10204。
在undo_management是AUTO的情况下，LOB 存储默认的是采用retention的管理方式，但是用dbms_metadata.get_ddl()抓取出的SQL却显示的是pctversion 方式，即使手动指定RETENTION 也是如此。
如果此时用CREATE TABLE AS 或者EXP 再IMP 一个LOB TABLE，新的LOB 属性就不是你想的跟源表一样了。
不管表空间是ASSM还是MSSM都有这个问题。EAGLE_FAN的说法是，也许ORACLE还没发现这个BUG。。不知道11G有没有修复。
不管怎样，建LOB TABLE还是手动指定PCTVERSION参数安全点。

SQL> select * from v$version;

BANNER
————————————————————————————————————————————————————————————
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bi
PL/SQL Release 10.2.0.4.0 – Production
CORE    10.2.0.4.0      Production
TNS for Solaris: Version 10.2.0.4.0 – Production
NLSRTL Version 10.2.0.4.0 – Production
SQL> create table lyn.water1(id number ,pic blob,des clob);

Table created.

SQL> create table lyn.water2(id number ,pic blob,des clob)
2 lob (pic) store as water2_pic(pctversion 5)
3 lob (des) store as water2_des(retention);

Table created.

SQL> select dbms_metadata.get_ddl(‘TABLE’,'WATER1′,’LYN’) from dual;

DBMS_METADATA.GET_DDL(‘TABLE’,'WATER1′,’LYN’)
——————————————————————————–

CREATE TABLE “LYN”.”WATER1″
(    “ID” NUMBER,
“PIC” BLOB,
“DES” CLOB
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NO
COMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS
2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE “LOBTB”

DBMS_METADATA.GET_DDL(‘TABLE’,'WATER1′,’LYN’)
——————————————————————————–
LOB (“PIC”) STORE AS (
TABLESPACE “LOBTB” ENABLE STORAGE IN ROW CHUNK 8192 PCTV
ERSION 10 –看上去默认的LOB是采用PCTVERSION
NOCACHE LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_PO
OL DEFAULT))
LOB (“DES”) STORE AS (
TABLESPACE “LOBTB” ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 1
0
NOCACHE LOGGING

DBMS_METADATA.GET_DDL(‘TABLE’,'WATER1′,’LYN’)
——————————————————————————–
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTE
NTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAU
LT))

SQL> select dbms_metadata.get_ddl(‘TABLE’,'WATER2′,’LYN’) from dual;

DBMS_METADATA.GET_DDL(‘TABLE’,'WATER2′,’LYN’)
——————————————————————————–

CREATE TABLE “LYN”.”WATER2″
(    “ID” NUMBER,
“PIC” BLOB,
“DES” CLOB
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NO
COMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS
2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE “LOBTB”

DBMS_METADATA.GET_DDL(‘TABLE’,'WATER2′,’LYN’)
——————————————————————————–
LOB (“PIC”) STORE AS “WATER2_PIC”(
TABLESPACE “LOBTB” ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 5
NOCACHE LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 214
7483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))
LOB (“DES”) STORE AS “WATER2_DES”(
TABLESPACE “LOBTB” ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10 –指定了RETENTION却仍显示的PCTVERSION
NOCACHE LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS
2147483645

DBMS_METADATA.GET_DDL(‘TABLE’,'WATER2′,’LYN’)
——————————————————————————–
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
)

SQL> select OWNER,TABLE_NAME,SEGMENT_NAME,PCTVERSION,RETENTION from dba_lobs where table_name in (‘WATER1′,’WATER2′);

OWNER      TABLE_NAME           SEGMENT_NAME                   PCTVERSION RETENTION
———- ——————– —————————— ———- ———-
LYN        WATER2               WATER2_PIC                              5
LYN        WATER2               WATER2_DES                                      1800 — 跟dbms_metadata.get_ddl()抓取的有出入了
LYN        WATER1               SYS_LOB0000010351C00002$$                       1800
LYN        WATER1               SYS_LOB0000010351C00003$$                       1800

SQL> select OBJ#,bitand(flags,32) from lob$ where obj# in (select object_id from dba_objects where wner=’LYN’ and object_name in (‘WATER1′,’WATER2′));

OBJ# BITAND(FLAGS,32)
———- —————-
10356                0 — 0代表用的pctversion
10356               32 – 32 代表用的retention
10351               32
10351              32

CREATE TABLE AS ：

SQL> create table lyn.water3 as select * from lyn.water1 where 1=2;

Table created.

SQL> select dbms_metadata.get_ddl(‘TABLE’,'WATER3′,’LYN’) from dual;

DBMS_METADATA.GET_DDL(‘TABLE’,'WATER3′,’LYN’)
——————————————————————————–

CREATE TABLE “LYN”.”WATER3″
(    “ID” NUMBER,
“PIC” BLOB,
“DES” CLOB
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NO
COMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS
2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE “LOBTB”

DBMS_METADATA.GET_DDL(‘TABLE’,'WATER3′,’LYN’)
——————————————————————————–
LOB (“PIC”) STORE AS (
TABLESPACE “LOBTB” ENABLE STORAGE IN ROW CHUNK 8192 PCTV
ERSION 10
NOCACHE LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_PO
OL DEFAULT))
LOB (“DES”) STORE AS (
TABLESPACE “LOBTB” ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 1
0
NOCACHE LOGGING

DBMS_METADATA.GET_DDL(‘TABLE’,'WATER3′,’LYN’)
——————————————————————————–
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTE
NTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAU
LT))

SQL> select OWNER,TABLE_NAME,SEGMENT_NAME,PCTVERSION,RETENTION from dba_lobs where wner=’LYN’ AND table_name like ‘WATER%’;

OWNER      TABLE_NAME           SEGMENT_NAME                   PCTVERSION RETENTION
———- ——————– —————————— ———- ———-
LYN        WATER1               SYS_LOB0000010351C00002$$                       1800
LYN        WATER1               SYS_LOB0000010351C00003$$                       1800
LYN        WATER2               WATER2_PIC                              5
LYN        WATER2               WATER2_DES                                      1800
LYN        WATER3               SYS_LOB0000010361C00002$$              10 — 可以看到不是RETENTION方式了
LYN        WATER3               SYS_LOB0000010361C00003$$              10
LYN        WATERFALLS           SYS_LOB0000010268C00002$$                       1800
LYN        WATERFALLS           SYS_LOB0000010268C00003$$                       1800
LYN        WATERFALLS           SYS_LOB0000010268C00004$$                       1800

9 rows selected.

SQL> select OBJ#,bitand(flags,32) from lob$ where obj# in (select object_id from dba_objects where wner=’LYN’ and object_name =’WATER3′);

OBJ# BITAND(FLAGS,32)
———- —————-
10361                0
10361                0

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22034023/viewspace-692541/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22034023/viewspace-692541/