Drop partition and rebuild global indexes "online"

Question

I am using Oracle 11g.

I have a requirement to drop a partition and rebuild global indexes. The query below does job well but BLOCKS all DML operations on the table until the indexes are rebuilt.

ALTER TABLE ABC DROP PARTITION PART1 UPDATE GLOBAL INDEXES;

But If I break the query into 2 parts and rebuilt indexes with ONLINE option separately, DML queries DOES NOT get blocked while indexes are being rebuilt

ALTER TABLE ABC DROP PARTITION PART1;
ALTER INDEX XYZ REBUILD ONLINE;

The down side of this approach is the time between the execution of drop partition and rebuilding index, those indexes will be unusable that might create performance problems.

So my question is, is there any option where I can drop partition and rebuild index "online" in one query? Currently I dont think we have following option.

ALTER TABLE ABC DROP PARTITION PART1 UPDATE GLOBAL INDEXES ONLINE;

Answer 1

Not in 11g.

Not even in 12c, but 12c makes life a bit easier, you can delay the "rebuild" part until a more appropriate time.

In 12c there is a new feature, called Asynchronous Global Index Maintenance. You can drop the partition, and that still leaves the global index usable, and marks the no longer needed index entries orphaned. Then later, you can clean up this index (DBMS_PART.CLEANUP_GIDX, or ALTER INDEX COALESCE CLEANUP, or the database can do it automatically for you as well (there is a pre-defined job for it called PMO_DEFERRED_GIDX_MAINT_JOB).

Answer 2

no global,online string，it's working with online rebuild index :

ALTER TABLE ABC DROP PARTITION PART1 UPDATE INDEXES;