Online Redefinition Leaves Global Index Unusable
(Doc ID 1498568.1)
Last updated on SEPTEMBER 20, 2022
Applies to:Oracle Database - Enterprise Edition - Version 10.2.0.1 to 220.127.116.11 [Release 10.2 to 11.1]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
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.
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!
In this Document