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'));

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

SQL>
create table TEST_PARTITION (c1 number) partition by range (c1)
    ( partition p100 values less than (100),
      partition p200 values less than (200),
      partition p300 values less than (300),
   partition pmax values less than (maxvalue));


Table created.

SQL> select high_value from dba_tab_partitions where table_name = 'TEST';

HIGH_VALUE
--------------------------------------------------------------------------------
100
200
300
MAXVALUE

SQL> alter table test add partition p40 values less than (400);
alter table test add partition p400 values less than (400)
                               *
ERROR at line 1:
ORA-14074: partition bound must collate higher than that of the last partition

SQL> alter table test split partition pmax at (400) into (partition p400, partition pmax);

Table altered.

SQL> select high_value from dba_tab_partitions where table_name = 'TEST_PARTITION';

HIGH_VALUE
--------------------------------------------------------------------------------
100
200
300
MAXVALUE
400