SQL Error: ORA-14300: While partitioning and subpartitioning

I have tried this earlier and it works:

• I have T1 which has no DATE or STATE which needs to be partitioned on DATE and sub-partitioned on STATE

• Hence for the ID present in T1, fetch the DATE and STATE from T2

• Create new table T3 which is partitioned form of T1 on DATE key, sub-partitioned on STATE key

• Pull all records from T1 and insert every row in T3 with not just two additional columns DATE and STATE. But also in their respective partition/sub-partition

My data is stored like this..

I used following create statement to create my table T3

CREATE TABLE SLABELSVALUE 
(   "DOC_ID" VARCHAR2(80 BYTE) NOT NULL ENABLE,
...
...
"DOC_DATETIME" DATE NOT NULL ENABLE, //additional in T3 compared to T1
"DOC_STATE" VARCHAR2(5 BYTE),  //additional in T3 compared to T1
CONSTRAINT...
PARTITION BY RANGE (DOC_DATETIME)  
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))  **Line added for auto partition
SUBPARTITION BY LIST (DOC_STATE)
 SUBPARTITION TEMPLATE(
 SUBPARTITION IN_PB VALUES('IN-PB') , //All states 
 SUBPARTITION IN_RJ VALUES('IN-RJ') ,
 ...
)  
(
PARTITION p0 VALUES LESS THAN (TO_DATE('01-MAY-2006','DD-MON-YYYY'))
//Ealier when it worked I defined all partitions for testing,
//When I included above lines `**` for auto partitioning it doesn't work
);

In my trial scenario, it worked when I did not include Interval partitioning mechanism.

I also defined all the partitions explicitly for testing.

• I have T1 data in production which reaches > 1 TB.

• 1 Billion rows each month

• First data back in 2006..that old

Error Summary

What am I trying now?

I do not want to explicitly mention 10 years X 12(months) partitions up till this date. Hence included INTERVAL(NUMTOYMINTERVAL(1, 'MONTH')) and I do not want to define partitions explicitly for future months.

And defined first partition as per Oracle

What is wrong now?

ORA-14300 partitioning key maps to a partition outside maximum permitted number of partitions

Have I checked for NULL values

Yes, as o

I had to explicitly define all my range partitions. Interval partitioning did not work well with sub-partitioning template.

Oracle does HARD Limit of 1m partitions to my interval partitions.

I defined partitions as

PARTITION jan06 VALUES LESS THAN (TO_DATE('01-FEB-2006','DD-MON-YYYY')),
...
ALL THE WAY FOR NEXT 20 YEARS..
...
PARTITION jan26 VALUES LESS THAN (TO_DATE('01-FEB-2026','DD-MON-YYYY')),

Refer here and there

StackExchange Database Administrators Q#132385, answer score: 2