前兩天把一個修改了一個分割槽表的索引後，執行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

分割槽交換成功。

交換分割槽時報錯：ORA-14098

相關文章