ORA-14099: all rows in table do not qualify for specified partition, ora-14099qualify
1.Create a partition table
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 ('1970-01-21 ', 'yyyy-mm-dd ')),
Partition p2 values less than (to_date ('1970-01-22 ', 'yyyy-mm-dd ')),
Partition p3 values less than (to_date ('1970-01-23 ', 'yyyy-mm-dd ')),
Partition p_max values less than (maxvalue)
);
Insert record
Insert into range_part_range values (1, to_date ('1970-01-21 ', 'yyyy-mm-dd'), 'A'); insert into range_part_range values (2, to_date ('1970-01-22 ', 'yyyy-mm-dd'),' B '); insert into range_part_range values (3, to_date ('1970-01-23 ', 'yyyy-mm-dd'), 'C ');
Retrieve records
Select count (*) from range_part_range; COUNT (*)
----------
3 select count (*) from range_part_range partition (p1); COUNT (*)
----------
0 select count (*) from range_part_range partition (p2); COUNT (*)
----------
1 select count (*) from range_part_range partition (p3); COUNT (*)
----------
1 select count (*) from range_part_range partition (p_max); COUNT (*)
----------
1
2.
Create a common table for partition exchangeCreate table range_tbl (id number, deal_date date, contents varchar2 (1000); insert record insert into range_tbl values (1000, to_date ('2017-01-25 ', 'yyyy-mm-dd'), 'J ');
3.
Perform partition exchangeAlter table range_part_range exchange partition p3 with table range_tbl
*
ERROR at line 1:
The ORA-14099: all rows in table do not qualify for specified partition
Note: The data in range_tbl is obviously not within the range of the p3 partition, but within the range of the p_max partition. Therefore, an error is reported..
4. solution 1:
Change the correct PartitionAlter table range_part_range exchange partition p_max with table range_tbl; select * from range_tbl; the record is now the original p_max record, select * from range_part_range partition (p_max ); the record is now the original range_tbl record. Solution 2:
Use without validationAlter table range_part_range exchange partition p3 with table range_tbl without validation; select * from range_tbl; records are now records of the original p3, select * from range_part_range partition (p3 ); the record is now the original range_tbl record.
Summary: 1. For swap partitions, if data exists in a common table, make sure that the value is within the range of swap partitions. Otherwise, an error is prompted. 2. Do not use without validation as far as possible. This will bypass verification. As in the previous example, partition p3 contains data not in the partition range, which may disrupt the partition purpose.
