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" <ahmed.fikri@xxxxxxxxxxx>
An: "list, oracle" <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
