Ora-14074 When Trying To Add Partition

刘泽宇

现象：
NOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product.  Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.

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

原因：
When the partition bound of the highest partition is anything other than
MAXVALUE, you can add a partition using the ALTER TABLE...ADD PARTITION
statement, otherwise 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.

处理方法：
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 <table_name> SPLIT PARTITION SALES_Q1_1998
AT (TO_DATE('01-JAN-1998','DD-MON-YYYY'))
INTO (PARTITION SALES_Q4_1997, PARTITION SALES_Q1_1998);


这里是如何处理的一个例子：

CREATE TABLE <table_name>
( 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 <table_name> 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 <table_name> 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 ='<table_name>';


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

select * from <table_name>;

select * from <table_name> partition (SALES_Q3_2000);

select * from <table_name> partition (SALES_Q4_1997);