ORA-14074: Partition Bound Must Collate Higher Than That Of The Last Partition.

Problem :

While adding a partition to a partitioned table, we got this error.

To avoid this error, the need to split the partition, instead of adding.

SQL> select partition_name,high_value from dba_tab_partitions where table_name='DBACLASS_QTAB';

PARTITION_NAME HIGH_VALUE

-------------- --------------------------------------------------------------------------------

DBACLASS_QM MAXVALUE

DBACLASS_Q3 TO_DATE(' 2023-03-04 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

DBACLASS_Q2 TO_DATE(' 2023-04-04 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

DBACLASS_Q1 TO_DATE(' 2023-06-04 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

Here we were trying to add a partition with high_value 03-APR-2023, which is between partition DBACLASS_Q3 and DBACLASS_QM.

So in this case for adding a new partition we need to split the maxvalue partition.

alter table dbaclass_QTAB SPLIT PARTITION DBACLASS_QM

AT (TO_DATE('03-APR-2023','DD-MON-YYYY'))

INTO ( PARTITION DBACLASS_Q4,

PARTITION DBACLASS_QM);

SQL> select partition_name,high_value from dba_tab_partitions where table_name='DBACLASS_QTAB';

PARTITION_NAME HIGH_VALUE

-------------- --------------------------------------------------------------------------------

DBACLASS_QM MAXVALUE

DBACLASS_Q4 TO_DATE(' 2023-02-04 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

DBACLASS_Q3 TO_DATE(' 2023-03-04 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

DBACLASS_Q2 TO_DATE(' 2023-04-04 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

DBACLASS_Q1 TO_DATE(' 2023-05-04 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA