Problem : Ora-14074 When Trying To Add Partition

Symptoms
Adding a partition to a table generates an ORA-14074 error.

.

Cause
You will need to split the partition when adding a partition to the beginning or middle of the
table.



Error: ORA 14074
Text: partition bound must collate higher than that of the last partition
Cause: Partition bound specified in ALTER TABLE ADD PARTITION statement did
not collate higher than that of the table's last partition, which is illegal.


Action: Ensure that the partition bound of the partition to be added collates
higher than that of the table's last partition.

.


Solution
In this example PARTITION SALES_Q1_1998 is the first partition in the table.
Use the alter table split partition command to add the new partitions.
To split this partition and add a new partition use this command.


ALTER TABLE range_sales SPLIT PARTITION SALES_Q1_1998
AT (TO_DATE('01-JAN-1998','DD-MON-YYYY'))
INTO (PARTITION SALES_Q4_1997, PARTITION SALES_Q1_1998);


Here's an example of how to get around this errror.


drop table range_sales;

CREATE TABLE range_sales
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (time_id)
(PARTITION SALES_Q1_1998 VALUES LESS THAN (TO_DATE('01-APR-1998','DD-MON-YYYY')),
PARTITION SALES_Q2_1998 VALUES LESS THAN (TO_DATE('01-JUL-1998','DD-MON-YYYY')),
PARTITION SALES_Q3_1998 VALUES LESS THAN (TO_DATE('01-OCT-1998','DD-MON-YYYY')),
PARTITION SALES_Q4_1998 VALUES LESS THAN (TO_DATE('01-JAN-1999','DD-MON-YYYY')),
PARTITION SALES_Q1_1999 VALUES LESS THAN (TO_DATE('01-APR-1999','DD-MON-YYYY')),
PARTITION SALES_Q2_1999 VALUES LESS THAN (TO_DATE('01-JUL-1999','DD-MON-YYYY')),
PARTITION SALES_Q3_1999 VALUES LESS THAN (TO_DATE('01-OCT-1999','DD-MON-YYYY')),
PARTITION SALES_Q4_1999 VALUES LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY')),
PARTITION SALES_Q1_2000 VALUES LESS THAN (TO_DATE('01-APR-2000','DD-MON-YYYY')),
PARTITION SALES_Q2_2000 VALUES LESS THAN (TO_DATE('01-JUL-2000','DD-MON-YYYY')),
PARTITION SALES_Q3_2000 VALUES LESS THAN (TO_DATE('01-OCT-2000','DD-MON-YYYY')),
PARTITION SALES_Q4_2000 VALUES LESS THAN (MAXVALUE));

-- error reported here
alter table range_sales add partition SALES_Q4_1997 VALUES LESS THAN
(TO_DATE('01-JAN-1998','DD-MON-YYYY'));


-- Split the partition to avoid the error
ALTER TABLE range_sales SPLIT PARTITION SALES_Q1_1998
AT (TO_DATE('01-JAN-1998','DD-MON-YYYY'))
INTO (PARTITION SALES_Q4_1997, PARTITION SALES_Q1_1998);



select TABLE_NAME, PARTITION_NAME, HIGH_VALUE from USER_TAB_PARTITIONS
where table_name ='RANGE_SALES';



insert into RANGE_SALES values
(1,1,TO_DATE('12-DEC-1996','DD-MON-YYYY'), 'a',1,1,1);

select * from RANGE_SALES;

select * from RANGE_SALES partition (SALES_Q3_2000);

select * from RANGE_SALES partition (SALES_Q4_1997);