ORA-14099 : all rows in table do not qualify for specified partition & ORA-14126

hash分区表在使用EXCHANGE PARTITION (交换分区)时出现错误：

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

 

Error: ORA 14099
Text: all rows in table do not qualify for specified partition
---------------------------------------------------------------------------
Cause: There is atleast one row in the non partitioned table which does not
 qualify for the partition specified in the ALTER TABLE EXCHANGE
 PARTITION
Action: Ensure that all the rows in the segment qualify for the partition.
 Perform the alter table operatin with the NO CHECKING option. Run
 ANALYZE table VALIDATE on that partition to find out the invalid rows
 and delete them.

 

 

解决方法：

一、如果被交换表中有数据，尝试交换到其它的分区。

 

二、交换分区时使用without validation 子句。

 

ALTER TABLE  &partition_name  EXCHANGE PARTITION   &t_partion_name   WITH TABLE &table_name     without validation UPDATE INDEXES ;

  

 

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
Connected as test

 

--1.创建分区表

SQL> CREATE TABLE t_partition_hash(ID NUMBER ,NAME Varchar2(10))
  2  PARTITION BY HASH(ID)
  3  PARTITIONS 4;
Table created

--2.插入数据

SQL> INSERT INTO t_partition_hash VALUES(1,'a');
SQL> INSERT INTO t_partition_hash VALUES(10,'b');
SQL> INSERT INTO t_partition_hash VALUES(20,'c');
SQL> INSERT INTO t_partition_hash VALUES(30,'d');
SQL> INSERT INTO t_partition_hash VALUES(40,'e');
SQL> INSERT INTO t_partition_hash VALUES(50,'f');
1 row inserted

--3.创建临时表tp_temp

SQL> CREATE TABLE tp_temp (ID NUMBER ,NAME Varchar2(10));
Table created
SQL> INSERT INTO tp_temp VALUES (60,'g');
1 row inserted

SQL> commit;
Commit complete

 

--4.查看表分区信息
SQL> SELECT TABLE_NAME, PARTITION_NAME
  2    FROM USER_TAB_PARTITIONS
  3   WHERE TABLE_NAME = 'T_PARTITION_HASH';

TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
T_PARTITION_HASH               SYS_P55
T_PARTITION_HASH               SYS_P56
T_PARTITION_HASH               SYS_P57
T_PARTITION_HASH               SYS_P58

 

 

SQL> select * from T_PARTITION_HASH partition(SYS_P57);
        ID NAME
---------- ----------
        20 c

SQL> select * from tp_temp;

        ID NAME
---------- ----------
        60 g

 

--5.将分区 SYS_P57 与tp_temp  中数据做交换，出现ORA-14099

SQL> ALTER TABLE t_partition_hash  EXCHANGE PARTITION   SYS_P57 WITH TABLE tp_temp UPDATE INDEXES;

 

ALTER TABLE t_partition_hash  EXCHANGE PARTITION   SYS_P57 WITH TABLE tp_temp UPDATE INDEXES

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

SQL> select * from T_PARTITION_HASH partition(SYS_P58);

        ID NAME
---------- ----------
         1 a

 

两种解决方法

 

一.换个分区SYS_P58 与tp_temp  中数据做交换，正常

SQL> ALTER TABLE t_partition_hash  EXCHANGE PARTITION   SYS_P58 WITH TABLE tp_temp UPDATE INDEXES;

Table altered

 

SQL> select * from T_PARTITION_HASH partition(SYS_P58);

        ID NAME
---------- ----------
        60 g

SQL> select * from tp_temp;

        ID NAME
---------- ----------
         1 a
 

SQL> ALTER TABLE t_partition_hash  EXCHANGE PARTITION   SYS_P57 WITH TABLE tp_temp UPDATE INDEXES
  2  without validation;

ALTER TABLE t_partition_hash  EXCHANGE PARTITION   SYS_P57 WITH TABLE tp_temp UPDATE INDEXES
without validation

ORA-14126: only a <parallel clause> may follow description(s) of resulting partitions

 

 二.使用without validation 子句（虽然可以成功，但不推荐）

SQL> ALTER TABLE t_partition_hash  EXCHANGE PARTITION   SYS_P57 WITH TABLE tp_temp    
  2 without validation UPDATE INDEXES ;

Table altered