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

ALTER TABLE "HR"."EMPLOYEE_YEAR_PART" ADD PARTITION "HIST_DATA_2015"  VALUES LESS THAN (TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE EDW_STG_PART_2015;

--> 

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

Solution:

RANGE Partition with last partition as MAXVALUE and we want to add a partition before MAXVALUE. Solution is to split the MAXVALUE partiton into

MAXVALUE_PARTITION = NEW_PARTITION_YOU_WANT_TO_ADD &  MAXVALUE

Table Name: EMPLOYEE_YEAR_PART

select table_name,TABLESPACE_NAME,partition_name,high_value from dba_tab_partitions where table_name='EMPLOYEE_YEAR_PART'  order by partition_position;

TABLE_NAME                     TABLESPACE_NAME      PARTITION_NAME                 HIGH_VALUE
------------------------------ -------------------- ------------------------------ --------------------------------------------------------------------------------
EMPLOYEE_YEAR_PART  HR_PART_2013    HIST_DATA_2013                 TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

EMPLOYEE_YEAR_PART  HR_PART_2014    HIST_DATA_2014                 TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

EMPLOYEE_YEAR_PART  HR_PART_MAX     HIST_DATA_MAX                  MAXVALUE

alter table "HR"."EMPLOYEE_YEAR_PART" split partition HIST_DATA_MAX at (to_date('2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) into
( PARTITION HIST_DATA_2015 TABLESPACE HR_PART_2015 ,partition HIST_DATA_MAX TABLESPACE HR_PART_MAX) update global indexes;

After Split:
============

 select table_name,TABLESPACE_NAME,partition_name,high_value from dba_tab_partitions where table_name='EMPLOYEE_YEAR_PART'  order by partition_position;

TABLE_NAME                     TABLESPACE_NAME      PARTITION_NAME                 HIGH_VALUE
------------------------------ -------------------- ------------------------------ --------------------------------------------------------------------------------
EMPLOYEE_YEAR_PART  HR_PART_2013    HIST_DATA_2013                 TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
                                                                                   N')

EMPLOYEE_YEAR_PART  HR_PART_2014    HIST_DATA_2014                 TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
                                                                                   N')

EMPLOYEE_YEAR_PART  HR_PART_2015    HIST_DATA_2015                 TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
                                                                                   N')

EMPLOYEE_YEAR_PART  HR_PART_MAX     HIST_DATA_MAX                  MAXVALUE