Re: [External] : Re: small bug related to deferred_segment_creation parameter

Hi Andy,

If you could provide me with the SR number, I’m happy to chase this down.

Thx,

Gerald Venzl | Distinguished Product Manager Email: gerald.venzl_at_oracle.com<mailto:gerald.venzl_at_oracle.com> | Phone: +1.650.633.0085<tel:+16506330085> Oracle ST & Database Development
400 Oracle Parkway | Redwood Shores | 94065 | USA

On Jul 22, 2021, at 10:49, Andy Sayer <andysayer_at_gmail.com<mailto:andysayer_at_gmail.com>> wrote:

You’re not the only one annoyed! I’ve had a bug open with support for 2+ years around an issue with automatic list partitioning with subpartition templates where deferred segment creation is ignored. I guess the more annoying thing is support lying once a month to tell me development are still working on the bug.

Thanks,
Andrew

On Thu, 22 Jul 2021 at 18:25, 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: 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<mailto:ahmed.fikri_at_t-online.de>" <ahmed.fikri_at_t-online.de<mailto:ahmed.fikri_at_t-online.de>> An: "list, oracle" <oracle-l_at_freelists.org<mailto: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