Re: small bug related to deferred_segment_creation parameter

It looks like you missed the point in my second paragraph:

*>> 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 20:23, ahmed.fikri_at_t-online.de < ahmed.fikri_at_t-online.de> wrote:

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