ORA-14642:partition exchange error

来源：互联网发布：免费淘宝装修生成编辑：程序博客网时间：2024/06/04 21:51

错误原因：

SQL> !oerr ora 1464214642, 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  0PL/SQL 过程已成功完成。SQL> execute show_hakan('pt1');Hakan factor for object 78189 (EASY.pt1) is 736 with flags   0PL/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     10000PL/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     10000PL/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 0PL/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

1 0