I have created below table with partitioning :

CREATE TABLE FRD_TBL_H (DAT NUMBER(8), DATST NUMBER(8), MEST VARCHAR2(1 CHAR), MEST1 VARCHAR2(1 CHAR), BELD CHAR(11 CHAR),VALID_FROM DATE, VALID_TO DATE) 
PARTITION BY RANGE(valid_to)
    INTERVAL(NUMTOYMINTERVAL(1, 'MONTH')) 
        ( PARTITION p0 VALUES LESS THAN (TO_DATE('01-01-1999', 'DD-MM-YYYY')))
         ENABLE ROW MOVEMENT;

I am trying to insert data into this table by keeping valid_to value to null and also i tried valid_to value to '01-01-1999' but getting below error:

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

I did partioning by month on valid_to column because the table has 70 million rows and i am writing below mere query for historization logic:

MERGE INTO FRD_TBL_H e 
    USING FRD_TBL h 
    ON (e.beld = h.beld) 
  WHEN MATCHED THEN 
    UPDATE SET e.valid_from = sysdate 
where e.valid_to = 01.01.1999 
  

CodePudding user response：

The maximum number of partitions in Oracle is 1024K - 1 = 1048575

What are you probably doing is the following. You created the table with day partitioning. (Not month as you posted).

CREATE TABLE FRD_TBL_H (DAT NUMBER(8), DATST NUMBER(8), MEST VARCHAR2(1 CHAR), MEST1 VARCHAR2(1 CHAR), BELD CHAR(11 CHAR),VALID_FROM DATE, VALID_TO DATE) 
PARTITION BY RANGE(valid_to)
    INTERVAL(NUMTODSINTERVAL(1, 'DAY')) 
        ( PARTITION p0 VALUES LESS THAN (TO_DATE('01-01-1999', 'DD-MM-YYYY')))
         ENABLE ROW MOVEMENT;

And used a sufficiently high valid_to date e.g. DATE'4999-01-01'

insert into FRD_TBL_H( VALID_TO) values (DATE'4999-01-01');  
ORA-14300: partitioning key maps to a partition outside maximum permitted number of partitions

A simple calculation shows, that the partition limit is indeed broken

select 1024*1024-1 max_part, DATE'4999-01-01' - TO_DATE('01-01-1999', 'DD-MM-YYYY') day_diff from dual;

 MAX_PART   DAY_DIFF
---------- ----------
   1048575    1095728

So this is expected bahavior.

The real question is, why do you use valid_to as the partition key and not valid_from.

With valid_from you will

• have not the problem with the null or dummy value

• the column will not change, so no need for row movement

• most queries use the predicate :1 >= valid_from so the pruning will work fine

To get an efficient acces to the open (last) version you may use composite partitioning with validfrom date range partitioning and list subpartitioning for open / closed versions.