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 )