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


偶然发现，用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

相关内容