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