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 
(   &quot;DOC_ID&quot; VARCHAR2(80 BYTE) NOT NULL ENABLE,
...
...
&quot;DOC_DATETIME&quot; DATE NOT NULL ENABLE, //additional in T3 compared to T1
&quot;DOC_STATE&quot; 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 &gt; 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 other blogs suggest I checked for NULL values in my columns and there is none.
What I suspect?
With Interval partitioning, sub-partitions might not be supported.
The statement that produces the error is:
INSERT INTO T3
SELECT T1.*, T2.DOC_DATETIME, T2.DOC_STATE
FROM T1
LEFT OUTER JOIN T2
ON (T1.DOC_ID =  T2.DOC_ID)
WHERE T2.DOC_DATETIME BETWEEN '01-APR-06' AND SYSDATE;

I am getting output if I remove the INSERT from line 1 and just keep SELECT.
Table structures:
T1 (Doc_ID varchar not null PK, label_ID varchar not null, label_val varchar not null)
T2 (DOc_ID varchar not null PK, Doc_State varchar not null, Doc_date DATE not null)
T3 (DOC_ID...,label_ID...,label_val..., Doc_State..., Doc_Date)

Please let me know if there is a solution.
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