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表,重新创建：
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.
尽量不要第二种方法,有可能会导致数据不准确
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/28939273/viewspace-1323252/ ，如需转载，请注明出处，否则将追究法律责任。
