指定LOB参数RETENTION,PCTVERSION的一个小BUG
在undo_management是AUTO的情况下，LOB 存储默认的是采用retention的管理方式，但是用dbms_metadata.get_ddl()抓取出的SQL却显示
偶然发现，用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 owner='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 owner='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 owner='LYN' and object_name ='WATER3');
OBJ# BITAND(FLAGS,32)
---------- ----------------
10361 0
10361 0