1. Create a partitioned table
CREATE TABLE Range_part_range (ID number, Deal_date date, Contents varchar2 ())
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)
);
Inserting Records
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 ');
Retrieving records
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 a normal table for partition switching CREATE TABLE Range_tbl (ID number, Deal_date date, Contents varchar2 ());Inserting RecordsINSERT into RANGE_TBL values (to_date (' 2015-01-25 ', ' yyyy-mm-dd '), ' J ');
3.
performing partition switching ALTER TABLE Range_part_range Exchange partition P3 with table Range_tbl
*
ERROR at line 1:
ora-14099:all rows in table does not qualify for specified partition
Note: The data in this range_tbl is clearly not within the range of the P3 partition, but is within the scope of the P_max partition and is therefore reported incorrectly .
4. Solution One:
change to a correct partition ALTER 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 Two:
use without validation ALTER TABLE Range_part_range Exchange partition P3 with table range_tbl without validation;SELECT * from Range_tbl; The record is now the original P3 record,SELECT * from Range_part_range partition (p3); The record is now the original RANGE_TBL record.
Summary :1. For the swap partition, if there is data in the normal table, make sure its value is within the partition range of the interchange, otherwise you will be prompted with an error. 2. Try not to use without validation, which will bypass the checksum, as in the example above, the P3 partition will contain a data that is not in its partition scope, it may disturb the purpose of the partition.
Ora-14099:all rows in table does not qualify for specified partition
