ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 指定LOB参数RETENTION,PCTVERSION的一个小BUG

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

原创 Linux操作系统 作者:wei-xh 时间:2011-04-14 21:40:25 0 删除 编辑
原文链接:
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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Oracle ACE组成员,DBGeeK用户组发起人。曾在DTCC、ORACLE技术嘉年华、Gdevops等公开场合做过数据库技术专题分享,2017年应Oracle邀请在世界最大的数据库会议OOW上做技术分享。组织翻译了《拨云见日,解密Oracle ASM内核》一书。

注册时间:2009-07-04

  • 博文量
    422
  • 访问量
    2315272