SQL Error: ORA-14300: While partitioning and subpartitioning

oraclepartitioning

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..

enter image description here

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 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.

Best Answer

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