交换分区时报错：ORA-14098

前两天把一个修改了一个分区表的索引后，执行job频频报错：
ORA-14098: index mismatch for tables in ALTER TABLE EXCHANGE PARTITION
这个错误是在分区表与一个普通表交换时发生的，检查发现，索引列是一致的，不知为何报错。

查阅相关资料后发现是因为分区表是全局索引，与用来交换的表的索引类型不一致造成的。下面简单重现错误：

1、创建表和索引
SQL> create table c1 (a number,b number)
2 partition by list(b)
3 (partition p1 values(1),
4 partition p2 values(2),
5 partition pd values(default))
6 ;

Table created

SQL> create table c2 as select * from c1;

Table created

SQL> create index idx_c1_a on c1(a) nologging;

Index created

SQL> create index idx_c2_a on c2(a);

Index created
表结构相同，索引列相同。

2、交换分区
SQL> alter table c1 exchange partition p1 with table c2 INCLUDING INDEXES WITHOUT VALIDATION;

alter table c1 exchange partition p1 with table c2 INCLUDING INDEXES WITHOUT VALIDATION

ORA-14098: ALTER TABLE EXCHANGE PARTITION 中的表索引不匹配

报错了。在metalink上查了一下，有如下信息：
Error: ORA 14098
Text: index mismatch for tables in ALTER TABLE EXCHANGE PARTITION
---------------------------------------------------------------------------
Cause: The two tables specified in the EXCHANGE have indexes which are not
equivalent
Action: Ensure that the indexes for the two tables have indexes which follow
this rule For every non partitioned index for the non partitioned
table, there has to be an identical LOCAL index on the partitioned
table and vice versa. By identical, the column position, type and size
have to be the same.

上面的信息说的很清楚了，如果要交换分区，分区表的索引必须为local类型，且索引列及顺序要与交换的表一致。

3、原因搞清楚了，解决就很简单那里

重新创建一个local类型的索引。
SQL> drop index IDX_C1_A;

Index dropped

SQL> create index idx_c1_a on c1(a) nologging local;

Index created

SQL> alter table c1 exchange partition p1 with table c2 INCLUDING INDEXES WITHOUT VALIDATION;

Table altered

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/20709695/viewspace-702963/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/20709695/viewspace-702963/