Oracle interval partitions are not created automatically with alter sub-partition template

Question

I am using oracle 12c Interval partitioning. I have created the range partition with 1 month interval and list sub-partition using a unique identifier (lets say LOGIN_INTFID).

In table DDL I have added the list of sub-partitions that were known to me at the time of table creation. Here is extract of table DDL:

CREATE TABLE TEST
(
    UNIQUE_ID   NUMBER(9) NOT NULL,
    LOGIN_INTFID    VARCHAR2(20) NOT NULL,
    LOGIN_SEQNO NUMBER(15) NOT NULL,
    LOGIN_DATE DATE DEFAULT SYSDATE NOT NULL
)
PARTITION BY RANGE (LOGIN_DATE)
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
SUBPARTITION BY LIST (LOGIN_INTFID) SUBPARTITION TEMPLATE (
SUBPARTITION SP1 VALUES ('ABC'),
SUBPARTITION SP2 VALUES ('DEF'),
)
(PARTITION TEST_Y2018M7D1  VALUES LESS 
THAN (TO_DATE('2018-07-01 23:59:59', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')))
;

With this, new partition and sub partitions are created successfully. Later on, I had added one more sub-partition using the following alter command:

ALTER TABLE TEST modify partition SYS_P7068 add subpartition SP3 values ('XYZ');

I have also altered table TEST with the intention that next time when a new partition gets created, this new sub-partition is included in the table automatically:

ALTER TABLE TEST SET SUBPARTITION TEMPLATE (SUBPARTITION SP3 VALUES('XYZ'));

However, this last part is not working as expected. New partitions are not getting created when I am trying to insert data containing XYZ sub partition value. New partitions are getting created only when sub-partition values ABC/DEF are inserted.

What am I doing wrong?

Marmite Bomber Marmite Bomber · Accepted Answer · 2018-08-02T10:14:17

You can't add a new subpartition to a template.

As the documetation states:

You can modify a subpartition template of a composite partitioned table by replacing it with a new subpartition template.

You have to define a new template consisting of the old and new subpartitions.

This will be valid for the partitions not yet created, for existing partition you have to add the subpartitions manually.

Example - after creation of your table you get one partition with two subpartitions

select PARTITION_NAME, SUBPARTITION_NAME,HIGH_VALUE  
from user_tab_subpartitions where table_name = 'TEST';

PARTITION_NAME SUBPARTITION_NAME HIGH_VALUE  
-------------- ----------------- ----------
TEST_Y2018     TEST_Y2018M7D1_   'ABC'     
TEST_Y2018     TEST_Y2018M7D1_   'DEF'

Inserting a row adds an other partition with the same two subpartitions:

insert into test (UNIQUE_ID,LOGIN_INTFID,LOGIN_SEQNO,LOGIN_DATE) 
values(1,'ABC',1,DATE'2018-08-02');

PARTITION_NAME SUBPARTITION_NAME    HIGH_VALUE 
-------------- -------------------- ----------
TEST_Y2018     TEST_Y2018M7D1_SP1   'ABC'    
TEST_Y2018     TEST_Y2018M7D1_SP2   'DEF'    
SYS_P14654     SYS_SUBP14652        'ABC'    
SYS_P14654     SYS_SUBP14653        'DEF'

Now you change the subpartition template - by defining all the new subspartitions

ALTER TABLE TEST SET SUBPARTITION TEMPLATE (
SUBPARTITION SP1 VALUES ('ABC'),
SUBPARTITION SP2 VALUES ('DEF'),
SUBPARTITION SP3 VALUES('XYZ'));

and adds an other row

insert into test (UNIQUE_ID,LOGIN_INTFID,LOGIN_SEQNO,LOGIN_DATE) 
values(1,'ABC',1,DATE'2018-09-02');

The new partition has now as expected three subpartitions

PARTITION_NAME SUBPARTITION_NAME    HIGH_VALUE  
-------------- -------------------- ----------
TEST_Y2018     TEST_Y2018M7D1_SP1   'ABC'    
TEST_Y2018     TEST_Y2018M7D1_SP2   'DEF'    
SYS_P14654     SYS_SUBP14652        'ABC'    
SYS_P14654     SYS_SUBP14653        'DEF'    
SYS_P14658     SYS_SUBP14655        'ABC'    
SYS_P14658     SYS_SUBP14656        'DEF'    
SYS_P14658     SYS_SUBP14657        'XYZ'

Oracle interval partitions are not created automatically with alter sub-partition template

1 Answers