ORA-14074 错误解决 -- 拆分分区

SQL>select  table_name , partition_name,high_value from user_tab_partitions where table_name='MMA_LOG'

TABLE_NAME           PARTITION_NAME                 HIGH_VALUE
-------------------- ------------------------------ --------------------------------------------------------------------------------
MMA_LOG              LOG_1103                       TO_DATE(' 2011-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
MMA_LOG              LOG_1106                       TO_DATE(' 2011-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
MMA_LOG              LOG_1109                       TO_DATE(' 2011-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
MMA_LOG              LOG_1112                       TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

由于2011前的数据比较多，我想把2011年之前的数据添加一个分区log_1012分区：

SQL> alter table mma_log add partition log_1012 values less than (date '2011-01-01') tablespace ts_1012;
alter table mma_log add partition log_1012 values less than (date '2011-01-01') tablespace ts_1012
                                  *
ERROR at line 1:
ORA-14074: partition bound must collate higher than that of the last partition

解决：

因为表上已经有了pmaxvalue分区,这个时候oracle是不直接允许增加分区的. 而是通过拆分分区实现,只要只用下列语句增加分区就不会发生该错误.
 
ALTER TABLE 【table_name 表名】 SPLIT PARTITION 【pmaxvalue -- 需要拆分的分区名】 at (to_date('2010-01-01','yyyy-mm-dd')) INTO (PARTITION p201001, PARTITION pmaxvalue);

 

SQL> alter table mma_log split partition log_1103 at (to_date('2011-01-01','yyyy-mm-dd')) into (partition log_1012,partition log_1103);
Table altered.

 

SQL> select  table_name , partition_name,high_value from user_tab_partitions where table_name='MMA_LOG';

TABLE_NAME           PARTITION_NAME                 HIGH_VALUE
-------------------- ------------------------------ --------------------------------------------------------------------------------
MMA_LOG              LOG_1012                       TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
MMA_LOG              LOG_1103                       TO_DATE(' 2011-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
MMA_LOG              LOG_1106                       TO_DATE(' 2011-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
MMA_LOG              LOG_1109                       TO_DATE(' 2011-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
MMA_LOG              LOG_1112                       TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

 

 

参考：http://hi.baidu.com/chenyucode/blog/item/2327f9302b13ca8ea9018e0b.html#0

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7583803/viewspace-708450/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7583803/viewspace-708450/