Learn Database Concepts

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...

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_partitio...