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);
Related Articles
- Create, Add or Split Oracle Database Partition Fails with ORA-14080 Error
- Oracle ORA-01658 Unable to Create INITIAL Extent for Segment in Tablespace Error
- ORA-02449 Oracle Drop Table Error
- ORA-00942 Table or View Does Not Exist Oracle Error
- Oracle EXP-00091 Error When Export Database
- Oracle Database Import Error 3113/3114
- Easily Duplicate, Copy or Backup Tables in Oracle, PostgreSQL, DB2 and SQLite with Create Table As SQL
- ORA-25153 Temporary Tablespace is Empty Error in Oracle
- Oracle PL/SQL ORA-00947 Not Enough Values Error
- IMP-00016 Required Character Set Conversion Not Supported Error when Import to Oracle Database
Entries (RSS)
April 7th, 2008 18:44
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
November 8th, 2006 18:44
[...] 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: [...]