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

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