Ora-14074 When Trying To Add Partition

Ora-14074 When Trying To Add Partition (文档 ID 727306.1)

转到底部

---

修改时间:2014-3-27类型:PROBLEM

In this Document Symptoms   Cause   Solution APPLIES TO: Oracle Database - Enterprise Edition - Version 10.2.0.1 to 11.2.0.0 [Release 10.2 to 11.2] Information in this document applies to any platform. SYMPTOMS Adding a partition to a table generates an ORA-14074 error. CAUSE 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. 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 error. 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);

Ora-14074 When Trying To Add Partition  

In this Document
Symptoms
Cause
Solution
APPLIES TO:

Oracle Database - Enterprise Edition - Version 10.2.0.1 to 11.2.0.0 [Release 10.2 to 11.2]
Information in this document applies to any platform.
SYMPTOMS

 

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

CAUSE

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.

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

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