Oracle ORA-14074 Create or Add New Partition Fails Error

Oracle database partition is a division or decomposition of a logical database and/or its constituting elements (indexes or transaction logs) into distinct, smaller, more manageable and independent parts or pieces. Database partitioning provides solution to problems faced by supporting very large tables and indexes, especially in data warehouse applications, which store and analyze large amounts of historical data. With partitioning or table splitting, SQL statements can access and manipulate the partitions rather than entire tables or indexes, and thus increase manageability, performance or availability of the Oracle database.

In Oracle Server or Oracle Enterprise Edition, when you want to add an additional new partition to a partitioned table by using the command ALTER TABLE <tablename> ADD PARTITION, the process fails and SQL*Plus will return the following error:

ORA-14074: partition bound must collate higher than that of the last partition

This error symptom is normally happened when the administrator tries to add or create a new partition to a partitioned table between two partitions, or the new partition is located at the beginning or in the middle of a table, or if the partition bound on the highest partition is MAXVALUE (original partition was created with the highest partition defined as MAXVALUE on the partition key).

The most likely case for the error is the MAXVALUE upper bound partition. You can check if there is such as high value exists in partitions defined for the table by using the following commands in SQL*Plus or TOAD:

select high_value from dba_tab_partitions where table_name = 'table_name';

You should see something like:

HIGH_VALUE
--------------------------------------------------------------------------------
10
20
30
MAXVALUE

In all of the above scenarios, Oracle doesn’t allow to create a new partition by using Add Partition SQL statement. Adding a new partition of these few instances can only be done with a split of partition by using SQL command of SPLIT PARTITION.

So the workaround or solution to the ORA-14074 error is by using the following SQL syntax to achieve the aim to add or create a new partition on an already partitioned table:

ALTER TABLE <tablename> SPLIT PARTITION

Full syntax will be:

ALTER TABLE <table_name> SPLIT PARTITION <partition_name> AT (<value>) 
INTO (PARTITION <new_partition>, PARTITION <next_partition {or partition_name as above line>}) 
UPDATE GLOBAL INDEXES;

Note: UPDATE GLOBAL INDEXES is optional.

For Example:

ALTER TABLE demo SPLIT PARTITION pmaxvalue at (40) INTO (PARTITION newpartition, PARTITION pmaxvalue);