ORA-14642:partition exchange error

错误原因：

SQL> !oerr ora 14642
14642, 00000, "Bitmap index mismatch for tables in ALTER TABLE EXCHANGE PARTITION"
// *Cause:  The two tables in the EXCHANGE have usable bitmap indexes, and the
//          INCLUDING INDEXES option has been specified and the tables have
//          different hakan factors. 
// *Action: Perform the exchange with the EXCLUDING INDEXES option or alter the
//          bitmap indexes to be unusable.

这个错误是因为交换的分区和表直接的hakan factor不一致引起的

实验过程：

SQL> create table pt1(c1 number,c2 char(1000)) partition by range(c1)
  2  (
  3  partition p1 values less than(100)
  4  );

表已创建。

SQL> insert into pt1 select rownum ,'a' from dual connect by level < 100;

已创建 99 行。

SQL> commit;

提交完成。

SQL> create bitmap index i1 on pt1(c1) local;

索引已创建。

SQL> alter table pt1 add partition p2 values less than(200);

表已更改。

SQL> create table t2 as select * from pt1;

表已创建。

SQL> update t2 set c1 = c1+100;

已更新99行。

SQL> commit;

提交完成。

SQL> create bitmap index i2 on t2(c1);

索引已创建。

SQL> execute show_hakan('t2');
Hakan factor for object 78195 (EASY.t2) is 736 with flags	  0

PL/SQL 过程已成功完成。

SQL> execute show_hakan('pt1');
Hakan factor for object 78189 (EASY.pt1) is 736 with flags	   0

PL/SQL 过程已成功完成。

SQL> alter table pt1 modify c2 not null;

表已更改。

SQL> execute show_hakan('pt1');
--Hakan factor for object 78189 (EASY.pt1) is 736 with flags     10000

PL/SQL 过程已成功完成。

SQL> drop index i1;

索引已删除。

SQL> create bitmap index i1 on pt1(c1) local;

索引已创建。

SQL> execute show_hakan('pt1');
Hakan factor for object 78189 (EASY.pt1) is 736 with flags     10000

PL/SQL 过程已成功完成。

SQL> drop index i1;

索引已删除。

SQL> alter table pt1 modify c2 null;

表已更改。

SQL> alter table pt1 modify c2 not null;

表已更改。

SQL> create bitmap index i1 on pt1(c1) local;

索引已创建。

SQL> execute show_hakan('pt1');
Hakan factor for object 78189 (EASY.pt1) is 8 with flags	 0

PL/SQL 过程已成功完成。

SQL> alter table pt1 exchange partition p2 with table t2 including indexes without validation;
alter table pt1 exchange partition p2 with table t2 including indexes without validation
*
第 1 行出现错误:
ORA-14097: ALTER TABLE EXCHANGE PARTITION 中的列类型或大小不匹配


SQL> alter table t2 modify c2 not null;

表已更改。

SQL> alter table pt1 exchange partition p2 with table t2 including indexes without validation;
alter table pt1 exchange partition p2 with table t2 including indexes without validation
*
第 1 行出现错误:
ORA-14642: ALTER TABLE EXCHANGE PARTITION 中表的位图索引不匹配

解决方法：

方法1：通过14529事件

alter session set events '14529 trace name context forever, level 1';

create table t1 as selct * from pt1;

alter session set events '14529 trace name context off';

14529事件可以确保在使用CREATE TABLE AS SELECT 语句是创建的表和源表具有相同的hakan factor

方法2：将位图索引设置为unusable或者使用EXCLUDING INDEXES