AW: small bug related to deferred_segment_creation parameter

it seems that I'm the only one in this list annoyed by the fact of loosing control about when a segment is created and when not. At least Oracle promises to get this control through the introduction of deferred_segment_creation parameter.Unfortunately, this parameter still have unnoticed bugs.  

Best regards
Ahmed      

-----Original-Nachricht-----
Betreff: small bug related to deferred_segment_creation parameter Datum: 2021-07-22T13:53:41+0200
Von: "ahmed.fikri_at_t-online.de" <ahmed.fikri_at_t-online.de> An: "list, oracle" <oracle-l_at_freelists.org>      

Hi everyone,

it seems that the deferred_segment_creation parameter does not work properly when splitting a table partition that contains a clob field. If the partition is empty and has no segment, splitting it creates two segments for the old and new partitions. I tested this using this block in 11.2.0.4 and 12.1.0.2 (also attached and run with a non dba user):  

DECLARE
  PROCEDURE ex(cmd VARCHAR2) IS BEGIN dbms_output.put_line(cmd); EXECUTE IMMEDIATE cmd; END;
  PROCEDURE ex_ignore_error(cmd VARCHAR2) IS BEGIN ex(cmd); EXCEPTION WHEN OTHERS THEN NULL; END;
  PROCEDURE split_part(tbl VARCHAR2,p_part_val VARCHAR2) IS BEGIN ex('alter table '||tbl||' split partition maxvalue at (to_date('''||p_part_val||''',''yyyymmdd''))   into (partition part_'||p_part_val||' tablespace users, partition maxvalue tablespace users)') ;END;
BEGIN
    ex('alter session set deferred_segment_creation = true'); ex_ignore_error('drop table tbl_test purge');   ex_ignore_error('drop table tbl_test2 purge');
/* create range-partitioned table without clob field */
  ex('create table tbl_test(id number, part_key date) tablespace users partition by range(part_key) (partition maxvalue values less than (maxvalue))');
/* create range-partitoned table having a clob field */
  ex('create table tbl_test2(id number, part_key date, clob_col clob) tablespace users partition by range(part_key) (partition maxvalue values less than (maxvalue))');
/* the table is empty. Splitting the maxvalue partition for the table without clob */
  split_part('tbl_test','20200101');
/* until here no segments is generated for tbl_test and tbl_test2 tables
 spltting the maxvalue partition for the table having clob field */   split_part('tbl_test2','20200101');
-- two segments are created for the table having clob field /* SELECT * FROM user_segments WHERE segment_name IN ('TBL_TEST','TBL_TEST2')
TBL_TEST2 PART_20200101 TABLE PARTITION ASSM USERS TBL_TEST2 MAXVALUE TABLE PARTITION ASSM USERS */

END;   Is this already known? Any Idea?
For me, this behavior is annoying and is considered a bug.  

Best regards
Ahmed  



--
http://www.freelists.org/webpage/oracle-l