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

I have a table AUDIT_LOGONS, it has 5 partitions in it and one partition is defined as MAXVALUE. All partitions has some data (see below screen) in it except the MAXVALUE partition. Now I want to add a new partition which has date values less than 2016-05-31

 But I am getting error ORA-14074

sql :

alter table AUDIT_LOGONS add partition AUDIT_LOGONS_P1 VALUES LESS THAN (TO_DATE(' 2016-05-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'));

and I get this error :

SQL Error: ORA-14074: partition bound must collate higher than that of the last partition
14074. 00000 -  "partition bound must collate higher than that of the last partition"
*Cause:    Partition bound specified in ALTER TABLE ADD PARTITION

Solution 1:

We can add a sub-partition to the partition that was set with MAXVALUE (AUDIT_LOGONS5 in this case). In below sql we are modifying the partition audit_logons5 adding a sub-parition audit_logons6 which will have all the data which has date below "2016-09-30"


ALTER TABLE MONTHLY_SALES MODIFY PARTITION AUDIT_LOGONS5 ADD SUB-PARTITION AUDIT_LOGONS6  VALUES LESS THAN (TO_DATE('2016-09-30 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN'));

Note : the partition can be renamed anytime

Solution 2 (This will not work for all):

One solution is to drop that Maxvalue (AUDIT_LOGONS5 in this case) partition if there is no data in it and then we can recreate another partitions with defined dates like below.

ALTER TABLE monthly_sales DROP PARTITION AUDIT_LOGONS5;

ALTER TABLE monthly_sales ADD PARTITION AUDIT_LOGONS5 VALUES LESS THAN (TO_DATE('2016-09-30 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN'));