Ora-14074 When Trying To Add Partition

你的名字 2022-08-24 11:55 133阅读 0赞

![t.gif][]

<table> 
 <tbody> 
  <tr> 
   <td align="left"><a title="单击此项可添加到收藏夹" href="https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=m63fuh1xo_14&amp;_afrLoop=379899449759282#" style="white-space:nowrap; font-size:11px; font-weight:normal; text-decoration:none" rel="nofollow"><img title="单击此项可添加到收藏夹" alt="单击此项可添加到收藏夹" src="https://support.oracle.com/epmos/common/images/favorites_qualifier_notsel.png" style="border-bottom:medium none; border-left:medium none; display:inline; vertical-align:middle; border-top:medium none; border-right:medium none"></a></td> 
   <td><img alt="" src="/images/20220823/9212f9ef25db4abf83166d7770ee1863.png" width="10" height="10"></td> 
   <td align="left"><span style="font-size:14px; color:black; text-align:right; padding-bottom:0px; padding-left:0px; padding-right:6px; font-weight:bold; padding-top:0px">Ora-14074 When Trying To Add Partition (文档 ID 727306.1)</span></td> 
   <td align="right"><a title="转到底部" href="https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=m63fuh1xo_14&amp;_afrLoop=379899449759282#" style="white-space:nowrap; font-size:11px; font-weight:normal; text-decoration:none" rel="nofollow"><img title="转到底部" alt="转到底部" src="https://support.oracle.com/epmos/ui/images/quicklink_smlarrowdown_enabled.gif" style="border-bottom:medium none; border-left:medium none; display:inline; vertical-align:middle; border-top:medium none; border-right:medium none"><span style="color:#03286; margin-left:3px; text-decoration:none">转到底部</span></a></td> 
   <td><img alt="" src="/images/20220823/9212f9ef25db4abf83166d7770ee1863.png" width="20" height="10"></td> 
  </tr> 
 </tbody> 
</table>

--------------------

<table> 
 <tbody> 
  <tr> 
   <td><span style="color:#75560; text-align:right; padding-bottom:0px; padding-left:0px; padding-right:6px; font-size:11px; font-weight:normal; padding-top:0px">修改时间:</span><span style="color:black; white-space:nowrap">2014-3-27</span><img alt="" src="/images/20220823/9212f9ef25db4abf83166d7770ee1863.png" width="20" height="10"><span style="color:#75560; text-align:right; padding-bottom:0px; padding-left:0px; padding-right:6px; font-size:11px; font-weight:normal; padding-top:0px">类型:</span><span style="color:black; white-space:nowrap">PROBLEM</span><img alt="" src="/images/20220823/9212f9ef25db4abf83166d7770ee1863.png" width="20" height="10"></td> 
   <td align="right"> 
    <table> 
     <tbody> 
      <tr> 
       <td><a title="为此文档评级" href="https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=m63fuh1xo_14&amp;_afrLoop=379899449759282#" style="white-space:nowrap; font-size:11px; font-weight:normal; text-decoration:none" rel="nofollow"><img title="为此文档评级" alt="为此文档评级" src="https://support.oracle.com/epmos/common/images/rate_ena_16.png" style="border-bottom:medium none; border-left:medium none; display:inline; vertical-align:middle; border-top:medium none; border-right:medium none"></a><img alt="" src="/images/20220823/9212f9ef25db4abf83166d7770ee1863.png" width="20" height="10"></td> 
       <td><a title="通过电子邮件发送此文档的链接" href="https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=m63fuh1xo_14&amp;_afrLoop=379899449759282#" style="white-space:nowrap; font-size:11px; font-weight:normal; text-decoration:none" rel="nofollow"><img title="通过电子邮件发送此文档的链接" alt="通过电子邮件发送此文档的链接" src="https://support.oracle.com/epmos/common/images/email_16.png" style="border-bottom:medium none; border-left:medium none; display:inline; vertical-align:middle; border-top:medium none; border-right:medium none"></a><img alt="" src="/images/20220823/9212f9ef25db4abf83166d7770ee1863.png" width="20" height="10"></td> 
       <td><a title="在新窗口中打开文档" href="https://support.oracle.com/epmos/faces/DocContentDisplay?id=727306.1" style="white-space:nowrap; font-size:11px; font-weight:normal; text-decoration:none" rel="nofollow"><img title="在新窗口中打开文档" alt="在新窗口中打开文档" src="https://support.oracle.com/epmos/common/images/offsite_ena.png" style="border-bottom:medium none; border-left:medium none; display:inline; vertical-align:middle; border-top:medium none; border-right:medium none"></a><img alt="" src="/images/20220823/9212f9ef25db4abf83166d7770ee1863.png" width="20" height="10"></td> 
       <td><a title="可打印页" href="https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=m63fuh1xo_14&amp;_afrLoop=379899449759282#" style="color:rgb(8,81,179); font-size:11px; font-weight:normal; text-decoration:none" rel="nofollow"><img title="可打印页" alt="可打印页" src="https://support.oracle.com/epmos/ui/images/print_ena.png" style="border-bottom:0px; border-left:0px; border-top:0px; border-right:0px"></a><img alt="" src="/images/20220823/9212f9ef25db4abf83166d7770ee1863.png" width="20" height="10"></td> 
      </tr> 
     </tbody> 
    </table> </td> 
  </tr> 
 </tbody> 
</table>

![t.gif][]

<table> 
 <tbody> 
  <tr> 
   <td><span style="font-size:12px; color:black"></span> <p style=""><strong>In this Document</strong><br> </p> 
    <table style=""> 
     <tbody> 
      <tr> 
       <td></td> 
       <td><a href="https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=m63fuh1xo_14&amp;_afrLoop=379899449759282#SYMPTOM" rel="nofollow">Symptoms</a></td> 
      </tr> 
     </tbody> 
    </table> 
    <table style=""> 
     <tbody> 
      <tr> 
       <td>&nbsp;</td> 
       <td><a href="https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=m63fuh1xo_14&amp;_afrLoop=379899449759282#CAUSE" rel="nofollow">Cause</a></td> 
      </tr> 
     </tbody> 
    </table> 
    <table style=""> 
     <tbody> 
      <tr> 
       <td>&nbsp;</td> 
       <td><a href="https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=m63fuh1xo_14&amp;_afrLoop=379899449759282#FIX" rel="nofollow">Solution</a></td> 
      </tr> 
     </tbody> 
    </table> 
    <hr style=""> <p style=""></p> <h2 style="">APPLIES TO:</h2> <span style="">Oracle Database - Enterprise Edition - Version 10.2.0.1 to 11.2.0.0 [Release 10.2 to 11.2]</span><br style=""> <span style="">Information in this document applies to any platform.</span><br style=""> <h2 style=""><a rel="nofollow"></a>SYMPTOMS</h2> <p style=""><br> <br> Adding a partition to a table generates an ORA-14074 error.</p> <h2 style=""><a rel="nofollow"></a>CAUSE</h2> <p style="">When the partition bound of the highest partition is anything other than<br> MAXVALUE, you can add a partition using the ALTER TABLE...ADD PARTITION<br> statement, otherwise You will need to split the partition when adding a partition to the beginning or middle of the table.<br> <br> Error: ORA 14074<br> Text: partition bound must collate higher than that of the last partition<br> Cause: Partition bound specified in ALTER TABLE ADD PARTITION statement did<br> not collate higher than that of the table's last partition, which is illegal.</p> <p style="">Action: Ensure that the partition bound of the partition to be added collates<br> higher than that of the table's last partition.</p> <h2 style=""><a rel="nofollow"></a>SOLUTION</h2> <p style="">In this example PARTITION SALES_Q1_1998 is the first partition in the table.<br> Use the alter table split partition command to add the new partitions.<br> To split this partition and add a new partition use this command.</p> 
    <div style="">
      ALTER TABLE range_sales SPLIT PARTITION SALES_Q1_1998 &nbsp; 
     <br> AT (TO_DATE('01-JAN-1998','DD-MON-YYYY')) &nbsp; 
     <br> INTO (PARTITION SALES_Q4_1997, PARTITION SALES_Q1_1998); 
    </div> <p style=""><br> Here's an example of how to get around this error.<br> <br> </p> 
    <div style="">
      drop table range_sales; 
     <br> 
     <br> CREATE TABLE range_sales 
     <br> ( prod_id NUMBER(6) 
     <br> , cust_id NUMBER 
     <br> , time_id DATE 
     <br> , channel_id CHAR(1) 
     <br> , promo_id NUMBER(6) 
     <br> , quantity_sold NUMBER(3) 
     <br> , amount_sold NUMBER(10,2) 
     <br> ) 
     <br> PARTITION BY RANGE (time_id) 
     <br> (PARTITION SALES_Q1_1998 VALUES LESS THAN (TO_DATE('01-APR-1998','DD-MON-YYYY')), 
     <br> PARTITION SALES_Q2_1998 VALUES LESS THAN (TO_DATE('01-JUL-1998','DD-MON-YYYY')), 
     <br> PARTITION SALES_Q3_1998 VALUES LESS THAN (TO_DATE('01-OCT-1998','DD-MON-YYYY')), 
     <br> PARTITION SALES_Q4_1998 VALUES LESS THAN (TO_DATE('01-JAN-1999','DD-MON-YYYY')), 
     <br> PARTITION SALES_Q1_1999 VALUES LESS THAN (TO_DATE('01-APR-1999','DD-MON-YYYY')), 
     <br> PARTITION SALES_Q2_1999 VALUES LESS THAN (TO_DATE('01-JUL-1999','DD-MON-YYYY')), 
     <br> PARTITION SALES_Q3_1999 VALUES LESS THAN (TO_DATE('01-OCT-1999','DD-MON-YYYY')), 
     <br> PARTITION SALES_Q4_1999 VALUES LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY')), 
     <br> PARTITION SALES_Q1_2000 VALUES LESS THAN (TO_DATE('01-APR-2000','DD-MON-YYYY')), 
     <br> PARTITION SALES_Q2_2000 VALUES LESS THAN (TO_DATE('01-JUL-2000','DD-MON-YYYY')), 
     <br> PARTITION SALES_Q3_2000 VALUES LESS THAN (TO_DATE('01-OCT-2000','DD-MON-YYYY')), 
     <br> PARTITION SALES_Q4_2000 VALUES LESS THAN (MAXVALUE)); 
     <br> 
     <br> -- error reported here 
     <br> alter table range_sales add partition SALES_Q4_1997 VALUES LESS THAN 
     <br> (TO_DATE('01-JAN-1998','DD-MON-YYYY')); 
     <br> 
     <br> 
     <br> -- Split the partition to avoid the error 
     <br> ALTER TABLE range_sales SPLIT PARTITION SALES_Q1_1998 
     <br> AT (TO_DATE('01-JAN-1998','DD-MON-YYYY')) 
     <br> INTO (PARTITION SALES_Q4_1997, PARTITION SALES_Q1_1998); 
     <br> 
     <br> 
     <br> select TABLE_NAME, PARTITION_NAME, HIGH_VALUE from USER_TAB_PARTITIONS 
     <br> where table_name ='RANGE_SALES'; 
     <br> 
     <br> 
     <br> insert into RANGE_SALES values 
     <br> (1,1,TO_DATE('12-DEC-1996','DD-MON-YYYY'), 'a',1,1,1); 
     <br> 
     <br> select * from RANGE_SALES; 
     <br> 
     <br> select * from RANGE_SALES partition (SALES_Q3_2000); 
     <br> 
     <br> select * from RANGE_SALES partition (SALES_Q4_1997); 
    </div> <br> </td> 
  </tr> 
 </tbody> 
</table>

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

[t.gif]: /images/20220823/9212f9ef25db4abf83166d7770ee1863.png