Re: small bug related to deferred_segment_creation parameter

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 < 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" <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