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 allowable value in all partitions in a table. The insert statement contains a partition column key value that is either null or exceeds the maximum number of partitions allowed. The table is divided into logical partitions based on a column known as the partition key column. The error ORA-14300: partitioning key maps to a partition outside maximum permitted number of partitions occurs because the value of the partition key column could not map to any logical partition to store in the partitioned table.
The table is created using logical partitioning. Oracle allows you to create as many logical partitions as you like (2 pow 10). There are 1048576 partitions in all. When the range interval is set to 1, values between 0 and 1048575 can be saved. If the value entered is null or more than 1048575, the value cannot be kept in any of the logical partitions. The error ORA-14300: partitioning key maps to a partition outside maximum permitted number of partition happens as a result of this.
When will this ORA-14300 error occur
The table is built on the basis of a logical partition. When you execute an insert statement with a key column that has a value of null or is beyond 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 stored 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 partitionsinsert 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 partitionsRoot Cause
A table is created with logical partition. You are running an insert statement with a partition key value of null. Or the partition key value is more than the maximum number of partitions allowed. The partition could not be identified by Oracle based on the partition key column value in the insert statement.
Solution 1
If you are executing an insert statement with partition key column value, check the null value in the insert statement. Providing a valid value in the partition key column in the insert statement will resolve the problem. The key column’s value should not be null. The value must fall within the maximum number of partitions allowed. Null is not an acceptable value in the partitioned table. If you provide a null value, the error “ORA-14300: partitioning key maps to a partition outside the maximum authorised number of partitions” occurs.
insert into EMP values(100);
1 row inserted. Solution 2
You can increase the interval value if the partition maximum high value in a table is less than the allowable partition value and you need to insert a value that is more than the acceptable partition value. By increasing the interval value, you will be able to input a wider range of numbers. This approach will be useless if the partition maximum high value is near to the maximum allowable partition.
Increasing the interval value resoles 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 101Solution
The high value in the above example is 101. The partition’s maximum high value is 1048575. As a result, we may widen the interval to accommodate a wider range of values 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 maximum high value of the partition is near to the maximum allowable partition, increasing the interval value will be useless. Partitions that have already been created are unchangeable. Because the last partition has already been generated, increasing the interval will not help in storing a value greater than the maximum allowed limit.
The only option to handle the value that is more than the maximum permissible partition size is to build a physical partition. To store a value bigger than the maximum partition value, remove the range interval and replace it with 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 1048575Solution
In this case, the partition high value has already been set to the maximum high value of 1048575. As a result, the physical partition is made 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.