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);

2 Responses to “Oracle ORA-14074 Create or Add New Partition Fails Error”

  1. Raj
    April 7th, 2008 18:44
    2

    we have a partition D20080330 already created abd the maxvalue is D30001231.
    Now we want to split the partition for D20080330.
    ALTER TABLE TABLE_NAME SPLIT PARTITION D30001231
    at (TO_DATE(’20080401′, ‘YYYYMMDD’)) INTO (PARTITION D20080331, PARTITION D30001231);
    fails with the error
    ORA-14080: partition cannot be split along the specified high bound.

    What am i missing in the above alter statement, any help would be appreciated

  2. Create, Add or Split Oracle Database Partition Fails with ORA-14080 Error » My Digital Life
    November 8th, 2006 18:44
    1

    [...] 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: [...]

Leave a Reply

You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Subscribe to comments feature has been disabled. To receive notification of latest comments posted, subscribe to My Digital Life Comments RSS feed or register to receive new comments in daily email digest.

New Articles

Incoming Search Terms for the Article

ORA-14074 - ORA-14074: partition bound must collate higher than that of the last partition - ora-14074 maxvalue - oracle alter table split partition maxvalue - ORA-14074: partition bound must collate higher than that of the last partition - split partition syntax - 14074 oracle error - oracle alter table split partition - add partition above max value oracle - how to enable partitioning in toad 9.2 - 14074 - add new partition IOT table - oracle ORA-14074 - añadir una particion + oracle - add partition sql syntax - creating new partition in a table - create partition oracle - how to add partition in oracle - how to create partition on a table in oracle - ORACLE CREATE PARTITION ON ALTER TABLE - oracle list partition max value - ORA-14074 : oracle - Oracle change the partition high value - oracle and 14074 - adding partitions oracle - add partition oracle - alter table split partition - define new partion oracle - add new partition in oracle - add partition error maxvalue - Alter table drop partition fails with temp tablespace - oracle add partitions - ORA-14080: partition cannot be split along the specified high bound - sql add partition -