ORA-14099: all rows in table do not qualify for specified partition，ora-14099qualify
1. 建立分區表
create table range_part_range(id number, deal_date date, contents varchar2(1000))
partition by range(deal_date)
(
partition p1 values less than (to_date('2015-01-21', 'yyyy-mm-dd')),
partition p2 values less than (to_date('2015-01-22', 'yyyy-mm-dd')),
partition p3 values less than (to_date('2015-01-23', 'yyyy-mm-dd')),
partition p_max values less than (maxvalue)
);
插入記錄
insert into range_part_range values(1, to_date('2015-01-21', 'yyyy-mm-dd'), 'a');insert into range_part_range values(2, to_date('2015-01-22', 'yyyy-mm-dd'), 'b');insert into range_part_range values(3, to_date('2015-01-23', 'yyyy-mm-dd'), 'c');
檢索記錄
select count(*) from range_part_range; COUNT(*)
----------
3select count(*) from range_part_range partition(p1); COUNT(*)
----------
0select count(*) from range_part_range partition(p2); COUNT(*)
----------
1select count(*) from range_part_range partition(p3); COUNT(*)
----------
1select count(*) from range_part_range partition(p_max); COUNT(*)
----------
1
2.
建立普通表，用於分區交換create table range_tbl (id number, deal_date date, contents varchar2(1000));插入記錄insert into range_tbl values(1000, to_date('2015-01-25', 'yyyy-mm-dd'), 'j');
3.
執行分區交換alter table range_part_range exchange partition p3 with table range_tbl
*
ERROR at line 1:
ORA-14099: all rows in table do not qualify for specified partition
注意：此處range_tbl中的資料明顯不是在p3分區的範圍之內，而是在p_max分區範圍內，因此報了錯。
4. 解決方案一：
換一個正確的分區alter table range_part_range exchange partition p_max with table range_tbl;select * from range_tbl;中記錄現在是原來p_max的記錄，select * from range_part_range partition(p_max);中記錄現在是原來range_tbl的記錄。解決方案二：
使用without validationalter table range_part_range exchange partition p3 with table range_tbl without validation;select * from range_tbl;中記錄現在是原來p3的記錄，select * from range_part_range partition(p3);中記錄現在是原來range_tbl的記錄。
總結：1. 對於交換分區，普通表中若有資料，要確保其值在交換的分區範圍內，否則會提示錯誤。2. 盡量不要用without validation，這樣會繞開校正，像上例，p3分區會包含一個不在其分區範圍內的資料，有可能擾亂了分區的目的。
