ORA-14099 错误解决

DB: 11.2.0.3.0

在测试把普通表修改为交换分区的时候,出现ORA-14099: all rows in table do not qualify for specified partition

模拟如下：

创建测试表yoon
SQL> create table yoon ( id number primary key,time date ) ;

Table created.

插入数据
SQL> insert into yoon select rownum,created from dba_objects;

74930 rows created.

提交
SQL> commit;

Commit complete.

建立分区表
SQL> create table yoon_new ( id number primary key,time date ) partition by range(time)
  2  (partition p1 values less than (to_date('2011-10-01','YYYY-DD-MM')),
  3   partition p2 values less than (maxvalue));

Table created.


SQL> ALTER TABLE YOON_NEW EXCHANGE PARTITION P1 WITH TABLE YOON;
ALTER TABLE YOON_NEW EXCHANGE PARTITION P1 WITH TABLE YOON
                                                      *
ERROR at line 1:
ORA-14099: all rows in table do not qualify for specified partition
 
原因：数据中含有大于'2011-10-01'的数据,但是分区只能存放小区'2011-10-01'的数据,所以报错


解决方法：
1.删除yoon_new表,重新创建：

 SQL> create table yoon_new ( id number primary key,time date ) partition by range(time)
  2  (partition p1 values less than (to_date('2015-10-01','YYYY-DD-MM')),
  3   partition p2 values less than (maxvalue));

Table created.

SQL> ALTER TABLE YOON_NEW EXCHANGE PARTITION P1 WITH TABLE YOON;

Table altered.

2.SQL> ALTER TABLE YOON_NEW EXCHANGE PARTITION P1 WITH TABLE YOON  WITHOUT VALIDATION;    

Table altered.
尽量不要第二种方法,有可能会导致数据不准确