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

This would be a good solution (very clever). But hopefully that oracle will understand that and get only the last state from the buffer cache. Hopefully that oracle doesn't robotically allocate the storage and disallocate it. As if the segment creation never took place.  

Best regards
Ahmed      

-----Original-Nachricht-----
Betreff: Re: [External] : Re: small bug related to deferred_segment_creation parameter
Datum: 2021-07-23T15:52:43+0200
Von: "Jonathan Lewis" <jlewisoracle_at_gmail.com> An: "Mark W. Farnham" <mwf_at_rsiz.com>        

An alternative to "drop empty segment" would be to do a back-door invocation of  

"alter table tbl_test2 truncate partition maxvalue drop all storage;"  

This (at least for 19.11) discards the segments but keeps the partition. The equivalent for composite partitions would be messier, of course. "Empty" is a bit tricky, of course, since there's a difference between "empty never been used" and "empty because it's all deleted". For partition maintenance Oracle already has a mechanism for knowing when a partition generated by a split is going to be empty, so that's the one case where the truncate option could be injected.  

Regards
Jonathan Lewis  

On Fri, 23 Jul 2021 at 13:04, Mark W. Farnham <mwf_at_rsiz.com <mailto:mwf_at_rsiz.com> > wrote:

  It seems likely that “create” fixes in place the “deferred” mode at a   different point in the code than maintenance. That’s a complete guess   since I don’t get to see the code, but it is compatible with “create”   correctly not creating “deferred” segments in the cases where it might   have to create an out of line segment.    

  Possibly it would be easier to create a “drop empty segments” maintenance   that to insert the correction to deferred segments. Since at least   logically Oracle could know range partition key references refer to a   single partition by name instead of stats more easily, that might be   better for the optimizer using partition stats. And, of course, clearing   up clutter in the dictionary as well as the possibly designed large   initial segment sizes. Gargantuan segments for Monday through Friday and   no segments at all for Saturday and Sunday is still a common pattern,   even in the days of globalization.    

  So this message is mostly to the Oracle lurkers: Consider a drop empty   segments between <low partition key reference> and <high partition key   reference> as a fix up if the maintenance code is too different from the   create code to pattern match the “bug except for restriction   documentation” behavior.    

  Quite often such restrictions are because resources could not be   prioritized rather than it being arcane to implement without the   restriction.    

  Since this behavior has been around, documented, for a long time, if you   change it a lot of folks will be asking for the “cleanup” code anyway.    

  Good luck.    

  Thanks Gerald and JL for pointing out it has “always” been this way,   ending the mystery.    

  mwf

  From: oracle-l-bounce_at_freelists.org
<mailto:oracle-l-bounce_at_freelists.org> [mailto:
  oracle-l-bounce_at_freelists.org <mailto:oracle-l-bounce_at_freelists.org> ]   On Behalf Of Jonathan Lewis
  Sent: Friday, July 23, 2021 4:38 AM
  To: list, oracle
  Subject: Re: [External] : Re: small bug related to   deferred_segment_creation parameter    

  I know that Gerald Venzi quoted the 21c reference manual, and going so   far forward to "retro-document" a detail can look like a cop-out; but in   this case you can find exactly the same text in the 11gR2 reference   manual - search for "Deferred segment creation" at the URL:    

<https://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_3001.htm#CJABFJEA>
       

  Regards

  Jonathan Lewis            

  On Fri, 23 Jul 2021 at 07:45, 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 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    <mailto:gerald.venzl_at_oracle.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> >               

   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