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.
盡量不要第二種方法,有可能會導致資料不準確