ORA-14074 错误解决 -- 拆分分区 博客分类: Oracle oracleORA-14074ORA14074
饮水思源:http://blog.itpub.net/7583803/viewspace-708450/
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