Create, Add or Split Oracle Database Partition Fails with ORA-14080 Error

»»»Create, Add or Split Oracle Database Partition Fails with ORA-14080 Error
Oracle table partitioning is important for optimum performance of the database, as it allows Oracle to process the specific and independat partition or part that is smaller in size, instead of entire full table or database. However, when you want to create a new partition to a table by adding or splitting partition, administrator may encounter errors such as ORA-14074. Other than that, Oracle error ORA-14080 can also happen, if you’re not careful enough with the following symptom:

ORA-14080: partition cannot be split along the specified high bound

The cause for ORA-14080 error is due to Oracle user attempted to split a partition along a bound which either collates higher than that of the partition to be split or lower than that of a partition immediately preceding the one to be split. In other word, the value that is been specified to split between the partition is incorrect, and part or all of the range of the partition values has been assigned or located in an existing partition defined in the database.

To solve the problem, the solution or workaround will be to ensure that the bound along which a partition is to be split collates lower than that of the partition to be split and higher that that of a partition immediately preceding the one to be split. The partition wants to be added or split must has value that are less than its closest higher value partition, yet has value that higher than the upper limit for its closest lower value partition.

For example, 3 partitions existed with high value as below:

Partition Name  High Value
-------------- ----------
p1                  20
p2                  40
p3                  60

If you want to add a new partition by splitting with p3 partition, you cannot specify the value of 70 or 30, as both are out of bound. Specify 30 if you want to split the partition of p3. For 70, you have to simply create or add new partition in alter table SQL statement command.

Oracle database administrator can use the following command to retrieve a chart like above to help determine the possible wrong value that cause the error:

SELECT partition_name, high_value
FROM dba_tab_partitions
WHERE table_name = 'table_name'
ORDER BY partition_name

By |2016-12-09T08:41:07+00:00December 9th, 2016|Categories: Databases|Tags: |0 Comments

About the Author:

LK is a technology writer for Tech Journey with background of system and network administrator. He has be documenting his experiences in digital and technology world for over 15 years.Connect with LK through Tech Journey on Facebook, Twitter or Google+.