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
