While adding a partition to a partitioned table, got below error.

SQL> alter table dbaclass_QTAB add partition dbaclass_q4 VALUES LESS THAN (TO_DATE(’01-APR-1998′,’DD-MON-YYYY’));
alter table dbaclass_QTAB add partition dbaclass_q4 VALUES LESS THAN (TO_DATE(’01-APR-1998′,’DD-MON-YYYY’))
*
ERROR at line 1:
ORA-14074: partition bound must collate higher than that of the last partition

SOLUTION:

This error occurs when we are trying to add a partition between two partitions where MAXVALUE is defined for that table.

So 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(' 1998-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DBACLASS_Q2    TO_DATE(' 1998-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DBACLASS_Q1    TO_DATE(' 1998-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

Here we were trying to add a partition with high_value 01-APR-1998, 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('01-APR-1998','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(' 1998-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DBACLASS_Q3    TO_DATE(' 1998-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DBACLASS_Q2    TO_DATE(' 1998-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DBACLASS_Q1    TO_DATE(' 1998-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA