ORA-14098: index mismatch for tables in ALTER TABLE EXCHANGE PARTITION

I am currently in the process of writing a loading procedure for a large table which is partitioned. The idea behind that is to change the physical structure of the table including change of partitioning method, LOB storage parameters and change of a column from being a real column filled by a default value to being a virtual column.

Basically I created the table with the new structure and then create interim tables per partition which then will become part of the new table by a simple “exchange partition” command.

But what happened is this:

SQL> alter table NEW_TABLE exchange partition SOME_PARTITION with table INTERIM_TABLE including indexes;
alter table NEW_TABLE exchange partition SOME_PARTITION with table INTERIM_TABLE including indexes
                                                            *
ERROR at line 1:
ORA-14098: index mismatch for tables in ALTER TABLE EXCHANGE PARTITION


Elapsed: 00:00:00.02

A quick look at all the indexes showed no difference, everything was pretty equal. So I switched to My Oracle Support and found ORA-14098 Even When Indexes and Constraints on the Exchange Table Match the Partitioned Table (Doc ID 1612345.1) which pretty much met my issue.

Since there is no fix for this issue as of now, I implemented the workaround which went fine:

SQL> alter table NEW_TABLE modify constraint NEW_TABLE_PK disable;

Table altered.

Elapsed: 00:00:00.26
SQL> alter table INTERIM_TABLE modify constraint INTERIM_TABLE_PK disable;

Table altered.

Elapsed: 00:00:00.08
SQL> alter table NEW_TABLE exchange partition IW05 with table INTERIM_TABLE including indexes;

Table altered.

Elapsed: 00:00:00.61

But the tricky stuff follows. When re-enabling the constraint, Oracle would validate it by scanning the whole table for inconsistencies. That would take a couple of hours if not days since my table is about 5T in size… So I use “enable novalidate” and then “rely” in the next step to tell the database that it can rely on the constraint even though it was not validated. That does the trick.

SQL> alter table NEW_TABLE modify constraint NEW_TABLE_PK enable novalidate;

Table altered.

Elapsed: 00:00:00.06
SQL> alter table NEW_TABLE modify constraint NEW_TABLE_PK rely;

Table altered.

Elapsed: 00:00:00.02
Advertisements