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;