AW: small bug related to deferred_segment_creation parameter

I managed to start my RAC 19.0.0. The bug is still there. Unfortunately I don't have any 19.3 instance. But I don't think that Oracle would solve the problem there. Maybe it is considered by oracle as feature and not as bug.  

Thanks and best regards
Ahmed      

-----Original-Nachricht-----
Betreff: AW: small bug related to deferred_segment_creation parameter Datum: 2021-07-22T21:44:50+0200
Von: "ahmed.fikri_at_t-online.de" <ahmed.fikri_at_t-online.de> An: "Jonathan Lewis" <jlewisoracle_at_gmail.com>, "list, oracle"
<oracle-l_at_freelists.org>
     

have just test it in 12.2.0.1 --> the bug is still there.  

I'm not sure if Oracle or another user has even noticed this problem to be solved in 19.3. (Who cares in times of cheap HDDs).  

I have a 19.3 (a RAC one), but I'm struggling to get it up (since months I didn't use it). Maybe someone can check whether the bug is still in 19.3  

Best regards
Ahmed      

-----Original-Nachricht-----
Betreff: AW: small bug related to deferred_segment_creation parameter Datum: 2021-07-22T21:23:14+0200
Von: "ahmed.fikri_at_t-online.de" <ahmed.fikri_at_t-online.de> An: "Jonathan Lewis" <jlewisoracle_at_gmail.com>, "list, oracle"
<oracle-l_at_freelists.org>
     

in the current project I'm working on, 12.1 is still in use. Maybe someone can test the script in 12.2 or 19.11.
In the meantime, I'll try to remove some dust from my private 12.2 and 19 instances and see if we have the same behavior.  

Best regards
Ahmed      

-----Original-Nachricht-----
Betreff: Re: small bug related to deferred_segment_creation parameter Datum: 2021-07-22T20:14:53+0200
Von: "Jonathan Lewis" <jlewisoracle_at_gmail.com> An: "ahmed.fikri_at_t-online.de" <ahmed.fikri_at_t-online.de>      

I'm not surprised that you didn't get a response in less than 3 hours - especially since you were talking about 11.2.0.4 and 12.1.0.2.  

However, it may be a little more interesting to point out that this example does the same thing in 19.11.0.0 - and still creates the segments even if you include the "segment creation deferred" option with each of the listed partitions in the split.  

Regards
Jonathan Lewis    

On Thu, 22 Jul 2021 at 12:53, ahmed.fikri_at_t-online.de
<mailto:ahmed.fikri_at_t-online.de> <ahmed.fikri_at_t-online.de
<mailto:ahmed.fikri_at_t-online.de> > wrote:
  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