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

SITUATION

An interval partition table structure is just created, then try to import the data from exported dump file. It causes ORA-14300 error. The same error occurs when insert records from another online backup table:

SQL> insert into tbl_test select * from tbl_test_old;
insert into tbl_test select * from tbl_test_old;
*
ERROR at line 1:
ORA-14300: partitioning key maps to a partition outside maximum permitted number of partitions

CAUSE

There are NULL values in partition key column.

SOLUTION

Update NULL values of  partition key column with non-null values, or remove those invalid records from source data.

Please note there are possible 1048575 partitions or sub-partitions for the partition key:

$ oerr ora 14300
14300, 00000, "partitioning key maps to a partition outside maximum permitted number of partitions"
// *Cause: The row inserted had a partitioning key that maps to a partition number greater than 1048575
// *Action Ensure that the partitioning key falls within 1048575 partitions or subpartitions.

Interval-Reference Partitioned Tables

Partitions in a reference-partitioned table corresponding to interval partitions in the parent table are created when inserting records into the reference partitioned table.

When creating an interval partition in a child table, the partition name is inherited from the associated parent table fragment. If the child table has a table-level default tablespace, then it is used as tablespace for the new interval partition; otherwise, the tablespace is inherited from the parent table fragment.

Interval-reference functionality requires that the database compatibility level (Oracle Database COMPATIBLE initialization parameter setting) be set to greater than or equal to 12.0.0.0.

  • Create a parent table and local index.
SQL>create table reserve 
(
res_id number primary key not null,
res_date date,
hotel_id number(3),
guest_id number
)
partition by range (res_date) INTERVAL (NUMTODSINTERVAL(1,'DAY'))
(partition P_FIRST values less than (TO_DATE('1900-01-01','YYYY-MM-DD'))
);

Table created.

SQL> create index idx_res_date on reserve(res_date) local;

Index created.
  • Create a child table.
SQL>create table transactions 
(
trans_id number not null,
res_id number not null,
trans_date date not null,
amt number,
constraint fk_trans_01 foreign key (res_id) references reserve
)  partition by reference (fk_trans_01);


Table created.
  • Query table and index  partitions.
SQL> select table_name, partition_name, high_value  
from user_tab_partitions  where table_name in ('RESERVE', 'TRANSACTIONS');

TABLE_NAME PARTITION_NAME HIGH_VALUE
--------------- --------------- ---------------------------------------------------------
RESERVE     P_FIRST TO_DATE(' 1900-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
TRANSACTIONS   P_FIRST

SQL>select INDEX_NAME,PARTITION_NAME,HIGH_VALUE
from user_ind_partitions;

INDEX_NAME PARTITION_NAME HIGH_VALUE
--------------- --------------- --------------------------------------
IDX_RES_DATE P_FIRST TO_DATE(' 1900-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
  • Insert data into parent table
 SQL> insert into reserve values (1,sysdate-5,1,1);

1 row created.

 SQL> insert into reserve values (2,sysdate-4,1,2);

1 row created.

SQL> insert into reserve values (3,sysdate-3,2,3);
1 row created.

SQL> insert into reserve values (4,sysdate,3,3);
1 row created.

SQL> commit;

Commit complete.
  • Query table and index  partitions.
SQL> select table_name, partition_name, high_value  
from user_tab_partitions  
where table_name in ('RESERVE', 'TRANSACTIONS');

TABLE_NAME PARTITION_NAME HIGH_VALUE
----------- --------------- ---------------------------------------
RESERVE     P_FIRST TO_DATE(' 1900-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
RESERVE   SYS_P301 TO_DATE(' 2014-10-16 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
RESERVE   SYS_P321 TO_DATE(' 2014-10-17 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
RESERVE   SYS_P341 TO_DATE(' 2014-10-18 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
RESERVE   SYS_P342 TO_DATE(' 2014-10-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
SQL> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE 
from user_ind_partitions;

INDEX_NAME PARTITION_NAME HIGH_VALUE
------------ -------------- ---------------------------------------------------------
IDX_RES_DATE SYS_P301 TO_DATE(' 2014-10-16 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
IDX_RES_DATE SYS_P321 TO_DATE(' 2014-10-17 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
IDX_RES_DATE SYS_P341 TO_DATE(' 2014-10-18 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
IDX_RES_DATE SYS_P342 TO_DATE(' 2014-10-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
IDX_RES_DATE P_FIRST TO_DATE(' 1900-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
  • Insert data into child table.
SQL> insert into transactions values(1,1,sysdate-5,1000);
1 row created.

SQL> insert into transactions values(2,2,sysdate-4,2000);
1 row created.

SQL> insert into transactions values(3,3,sysdate-3,3000);
1 row created.

SQL> insert into transactions values(4,4,sysdate,4000);
1 row created.

SQL> commit;
Commit complete.
  • Query tables partitions.
SQL> select table_name, partition_name, high_value  
from user_tab_partitions  where table_name in ('RESERVE', 'TRANSACTIONS');


TABLE_NAME PARTITION_NAME HIGH_VALUE
---------- -------------- ----------------------------------------
RESERVE P_FIRST TO_DATE(' 1900-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
RESERVE SYS_P301 TO_DATE(' 2014-10-16 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
RESERVE SYS_P321 TO_DATE(' 2014-10-17 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
RESERVE SYS_P341 TO_DATE(' 2014-10-18 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
RESERVE SYS_P342 TO_DATE(' 2014-10-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
TRANSACTIONS P_FIRST
TRANSACTIONS SYS_P301
TRANSACTIONS SYS_P321
TRANSACTIONS SYS_P341
TRANSACTIONS SYS_P342

10 rows selected.
  • Drop a partition of parent table.
SQL> alter table RESERVE drop partition SYS_P301 update indexes;

Table altered.
SQL> select table_name, partition_name  
from user_tab_partitions
where table_name in ( 'RESERVE','TRANSACTIONS');

TABLE_NAME PARTITION_NAME --------------- --------------- RESERVE P_FIRST RESERVE SYS_P321 RESERVE SYS_P341 RESERVE SYS_P342 TRANSACTIONS P_FIRST TRANSACTIONS SYS_P321 TRANSACTIONS SYS_P341 TRANSACTIONS SYS_P342

8 rows selected.
SQL> select INDEX_NAME,PARTITION_NAME from user_ind_partitions;

INDEX_NAME PARTITION_NAME --------------- --------------- IDX_RES_DATE SYS_P321 IDX_RES_DATE SYS_P341 IDX_RES_DATE SYS_P342 IDX_RES_DATE P_FIRST

We can see both index partition and  child partition are dropped automatically.