Specify LOB parameter RETENTION, a small BUG in PCTVERSION

When undo_management is AUTO, the default LOB storage adopts retention management, but the SQL captured by dbms_metadata.get_ddl () is displayed.

When undo_management is AUTO, the default LOB storage adopts retention management, but the SQL captured by dbms_metadata.get_ddl () is displayed.

Occasionally, a small BUG occurs when dbms_metadata.get_ddl () is used to capture the SQL statement for creating a TABLE with LOB.
The version is 10204.
When undo_management is AUTO, LOB storage uses the retention management method by default, but the SQL captured by dbms_metadata.get_ddl () displays the pctversion method ,, this is true even if you manually specify a RETENTION.
If you use create table as or EXP to CREATE another lob table, the new LOB attribute is not the same AS that of the source TABLE.
This problem occurs no matter whether the tablespace is ASSM or MSSM. The EAGLE_FAN statement is: Maybe Oracle hasn't found this BUG .. I don't know if there is any repair for 11G.
In any case, create a lob table or manually specify the PCTVERSION parameter security point.

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', 'line') from dual;

DBMS_METADATA.GET_DDL ('table', 'water1', 'line ')
--------------------------------------------------------------------------------

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', 'line ')
--------------------------------------------------------------------------------
LOB ("PIC") store (
TABLESPACE "LOBTB" enable storage in row chunk 8192 PCTV
ERSION 10-The default LOB is 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 (
TABLESPACE "LOBTB" enable storage in row chunk 8192 PCTVERSION 1
0
NOCACHE LOGGING

DBMS_METADATA.GET_DDL ('table', 'water1', 'line ')
--------------------------------------------------------------------------------
STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTE
NT 2147483645
PCTINCREASE 0 FREELISTS 1 freelist groups 1 BUFFER_POOL DEFAU
LT ))

SQL> select dbms_metadata.get_ddl ('table', 'water2', 'line') from dual;

DBMS_METADATA.GET_DDL ('table', 'water2', 'line ')
--------------------------------------------------------------------------------

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', 'line ')
--------------------------------------------------------------------------------
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-specifies the PCTVERSION that is displayed after RETENTION.
NOCACHE LOGGING
STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS
2147483645

DBMS_METADATA.GET_DDL ('table', 'water2', 'line ')
--------------------------------------------------------------------------------
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 -- there is a discrepancy with dbms_metadata.get_ddl () crawling
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 = 'line' and object_name in ('water1 ', 'water2 '));

OBJ # BITAND (FLAGS, 32)
--------------------------
10356 0 -- 0 indicates the pctversion used
10356 32-32 indicates the retention used
10351 32
10351 32

Create table:

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

Table created.

SQL> select dbms_metadata.get_ddl ('table', 'water3', 'line') from dual;

DBMS_METADATA.GET_DDL ('table', 'water3', 'line ')
--------------------------------------------------------------------------------

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', 'line ')
--------------------------------------------------------------------------------
LOB ("PIC") store (
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 (
TABLESPACE "LOBTB" enable storage in row chunk 8192 PCTVERSION 1
0
NOCACHE LOGGING

DBMS_METADATA.GET_DDL ('table', 'water3', 'line ')
--------------------------------------------------------------------------------
STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTE
NT 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 = 'line' 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 -- the RETENTION mode is not supported.
LYN WATER3 SYS_LOB0000010361C00003 $10
Lyn waterfalls SYS_LOB0000010268C00002 $1800
Lynn 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 = 'line' and object_name = 'water3 ');

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