ORA-14099: all rows in table do not qualify for specified partition, ora-14099qualify

Source: Internet
Author: User

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.