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

ORA-14300: partitioning key maps to a partition outside maximum permitted number of partitions error occurs when the partition key value is null or outside of the maximum permissible value in all partitions in a table. The insert statement has a key value that is either null or outside of maximum permitted number of partitions value. The error ORA-14300: partitioning key maps to a partition outside maximum permitted number of partitions occurs because the key value could not map the logical partition to store in the partitioned table.

The table is created using logical partitions. Oracle allows you to build up to (2 pow10) logical partitions. The total number of partitions is 1048576. When the range interval is set to 1, you can store values ranging from 0 to 1048575. If the inserted value is null or more than 1048575, the value cannot be stored in any of the logical partitions. As a result, the error ORA-14300: partitioning key maps to a partition outside maximum permitted number of partition occurs.



When will this ORA-14300 error occur

The table is built on the basis of a logical partition. When you run an insert statement with a key column with a value of null or outside the maximum allowed number of partitions, the error “ORA-14300: partitioning key maps to a partition outside maximum permitted number of partition” occurs.

The key value must be within the allowed range in order for the value to be saved in any of the partitions. If the key value exceeds the limit, the partition cannot be identified. As a result, the error occurred.

create table emp (id int)
  partition by range(id) interval(1)
  ( partition p0 values less than (1));
insert into EMP values(null); 

Error starting at line : 37 in command -
insert into EMP values(null)
Error report -
ORA-14300: partitioning key maps to a partition outside maximum permitted number of partitions
insert into EMP values(1048575); 

Error starting at line : 19 in command -
insert into EMP values(1048575)
Error report -
ORA-14300: partitioning key maps to a partition outside maximum permitted number of partitions


Root Cause

A table is created with logical partition. You are executing an insert statement that contains partition key value has null. Or the partition key value is outside maximum permitted number of partitions. Oracle could not identify the partition based on the key column value in the insert statement.



Solution 1

Verify the null value in the insert statement if you are inserting a null value. In the insert statement, providing a valid value in the key column will resolve the error. The value of the key column should not be null. The value should be accommodated within the maximum number of partitions permitted. In the partitioned table, null is not an allowed value. The error ORA-14300: partitioning key maps to a partition outside maximum permitted number of partitions occurs if you insert null value.

insert into EMP values(100);

1 row inserted. 


Solution 2

If the partition maximum high value in a table is less than the permissible partition value and you need to insert a value that is more than the permitted partition value, you can increase the interval value. Increasing the interval value will allow you to insert a wider range of values. If the partition maximum high value is close to the maximum allowed partition, this approach will be ineffective.

Increasing the interval value will resole the error ORA-14300: partitioning key maps to a partition outside maximum permitted number of partitions.

Problem

create table emp (id int)
  partition by range(id) interval(1)
  ( partition p0 values less than (1));

insert into EMP values(100);
insert into EMP values(1048575);

Error starting at line : 19 in command -
insert into EMP values(1048575)
Error report -
ORA-14300: partitioning key maps to a partition outside maximum permitted number of partitions 

Verify High value

select table_name, partition_name, high_value FROM ALL_TAB_PARTITIONS where table_name='EMP'; 

table_name, partition_name, high_value
EMP	P0	1 
EMP	SYS_P1121	101

Solution

In the above example the high value is 101. The maximum partition high value is 1048575. Hence wee can increase the interval to accommodate wider range of the value in the remaining partitions. This will resolve the ORA-14300: partitioning key maps to a partition outside maximum permitted number of partitions error.

alter table EMP set interval(2); 

insert into EMP values(1048575);
1 row inserted.


Solution 3

If the partition maximum high value is close to the maximum allowed partition, increasing the interval value will be ineffective. Partitions that have already been created cannot be changed. Because the last partition has already been created, increasing the interval will not help in storing the value above the maximum permitted limit.

The only way to accommodate the value outside of the maximum allowable partition is to create a physical partition. To store a value greater than the maximum partition value, delete the range interval and add the physical partition.

Problem

create table emp (id int)
  partition by range(id) interval(1)
  ( partition p0 values less than (1));

insert into EMP values(1048574);
insert into EMP values(1048575);

Error starting at line : 19 in command -
insert into EMP values(1048575)
Error report -
ORA-14300: partitioning key maps to a partition outside maximum permitted number of partitions 

Verify High value

select table_name, partition_name, high_value FROM ALL_TAB_PARTITIONS where table_name='EMP'; 

table_name, partition_name, high_value
EMP	P0	1 
EMP	SYS_P1121	1048575

Solution

In this example the partition high value is already reached to maximum high value 1048575. Hence the physical partition is created using the alter partition command.

alter table EMP set interval();  

alter table EMP add partition p_1048575 values less than ( 1048576 );

insert into EMP values(1048575);
1 row inserted.



Leave a Reply