Online Redefinition Leaves Global Index Unusable
Last updated on OCTOBER 16, 2012
Applies to:Oracle Server - Enterprise Edition - Version 10.2.0.1 to 22.214.171.124 [Release 10.2 to 11.1]
Information in this document applies to any platform.
Using online redefinition on a partitioned table with a global index, leaves the global index UNUSABLE once the redefinition is complete.
The issue is cause by using online redefinition:
However, it does not include the UPDATE [GLOBAL] INDEX clause.
What the document says:
Internally while finishing the redefinition index must be updated. The documentation for alter partitioning tables as follows:
Partitioned Tables and Indexes
Maintenance of Global Partitioned Indexes:
By default, the following operations on partitions on a heap-organized table mark all global indexes as unusable:
These indexes can be maintained by appending the clause UPDATE GLOBAL INDEXES to the SQL statements for the operation.
The two advantages to maintaining global indexes:
* The index remains available and online throughout the operation. Hence no other applications are affected by this operation.
* The index doesn't have to be rebuilt after the operation.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms