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

Hi all,  

My impression is that every time Oracle detects a bug it doesn't seem easy to resolve, add two lines of documentation that says this is a restriction. I've seen some examples of this on this forum. We even have conflicting documentation, one saying this is a limitation, the other saying it's a feature, and so on.
But that's just my impression, and doesn't mean it is true.  

Many thanks to Jonathan and Andy.  

Best regards
Ahmed        

-----Original-Nachricht-----
Betreff: Re: [External] : Re: small bug related to deferred_segment_creation parameter
Datum: 2021-07-23T02:44:53+0200
Von: "Gerald Venzl" <gerald.venzl_at_oracle.com> An: "ahmed.fikri_at_t-online.de" <ahmed.fikri_at_t-online.de>      

Hi all,  

It is reproducible not only in 19.11 but will also be in 21c as this is not a bug but a documented restriction (see alter_table_partitioning section) <https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/ALTER-TABLE.html> :  

• Deferred segment creation is not supported for partition maintenance operations that create new segments on tables with LOB columns; segments will always be created for the involved (sub)partitions.

Furthermore, you can see that this restriction holds true for all partition maintenance operations and not only the SPLIT operation.  

Nope this helps!  

Thx,

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

    On Jul 22, 2021, at 15:14, Jonathan Lewis <jlewisoracle_at_gmail.com     <mailto:jlewisoracle_at_gmail.com> > wrote:

    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     <mailto:ahmed.fikri_at_t-online.de> <ahmed.fikri_at_t-online.de     <mailto: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
      <mailto:jlewisoracle_at_gmail.com> >
      An: "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> >
       
       
       
      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