Hi Andy,
If you could provide me with the SR number, I’m happy to chase this down.
Thx,
---------------
Gerald Venzl | Distinguished Product Manager
Email: gerald.venzl@xxxxxxxxxx<mailto:gerald.venzl@xxxxxxxxxx> | Phone:
+1.650.633.0085<tel:+16506330085>
Oracle ST & Database Development
400 Oracle Parkway | Redwood Shores | 94065 | USA
On Jul 22, 2021, at 10:49, Andy Sayer
<andysayer@xxxxxxxxx<mailto:andysayer@xxxxxxxxx>> wrote:
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@xxxxxxxxxxx<mailto:ahmed.fikri@xxxxxxxxxxx>
<ahmed.fikri@xxxxxxxxxxx<mailto:ahmed.fikri@xxxxxxxxxxx>> 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@xxxxxxxxxxx<mailto:ahmed.fikri@xxxxxxxxxxx>"
<ahmed.fikri@xxxxxxxxxxx<mailto:ahmed.fikri@xxxxxxxxxxx>>
An: "list, oracle" <oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>>
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
