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

I got ” ORA-14074 – partition bound must collate higher than that of the last partition ”  error in Oracle database.

 

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

 

Details of error are as follows.

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

Cause - Partition bound specified in ALTER TABLE ADD PARTITION statement did not collate 
higher than that of the table's last partition, which is illegal.

Action - Ensure that the partition bound of the partition to be added collates higher than
 that of the table's last partition.


ALTER TABLE TABLE_NAME ADD PARTITION P01012021 VALUES LESS THAN (TO_DATE('01-JAN-2021','DD-MON-YYYY'))

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

 

 

partition bound must collate higher than that of the last partition

This ORA-14074 error is related with the Partition bound specified in ALTER TABLE ADD PARTITION statement did not collate higher than that of the table’s last partition, which is illegal.

To solve this error, Ensure that the partition bound of the partition to be added collates higher than that of the table’s last partition. You should split the partition instead of adding new partition.

 

You should drop the Maxvalue partition if there is no data in it and then we can recreate another partitions.

 

 

Another case is as follows.

 

ALTER TABLE <user_name>.T1
ADD PARTITION T1_20100701
VALUES LESS THAN (TO_DATE(' 2010-07-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE FW_PROD;

the following error occurs:

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

 

 

The table is created using:

CREATE TABLE TEST
(
  FW_INSERT_DATE            DATE                NOT NULL,
  FW_COMP              VARCHAR2(8 BYTE)    NOT NULL,
  FW_STAT                 NUMBER(3),
  FW_SOURC               VARCHAR2(32 BYTE),
...
  BAD_FR_COUNT           VARCHAR2(32 BYTE),
  EVENT_TIMESTAMP           VARCHAR2(32 BYTE),  <------------------------partition key
...
PARTITION BY RANGE (EVENT_TIMESTAMP)
( 
  PARTITION T1_20100302 VALUES LESS THAN (TO_DATE(' 2010-03-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
...

 

This is the first time issuing the add partition command on the table.

 

Most probably some changes were made at the nls settings level.

 

The issue seems to be related to nls settings of the session as when using:

SQL> alter session set nls_date_format='SYYYY-MM-DD HH24:MI:SS';

The alter table is working no matter if the partition key type is varchar2.

Having the partition key on a column with a date datatype doesn’t return an even with nls_date_format set.

 

Use the correct nls format when the partition key is varchar or char datatype and the error does not occur.

alter session set nls_date_format='SYYYY-MM-DD HH24:MI:SS';

SQL> ALTER TABLE T1
ADD PARTITION T1_20100701 VALUES LESS THAN (TO_DATE(' 2010-07-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE USERS;

 

 

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )

 

 676 views last month,  1 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply