In Oracle 12c (12.1) we have a huge partitioned table with interval partitioning.
Beside local indexes, there are two global indexes on it.
Dropping old partitions with UPDATE INDEXES clause helps the global indexes keeping valid.
Next night, the feature called Asynchronous Global Index Maintenance background job has started an
ALTER INDEX xxx COALESCE CLEANUP
command, which runs since several days and cleans up the global indexes from orphan entries of dropped partitions.
Unfortunately, we need to create another index on this table with different parameters, but it's not possible due to resource busy error because of a shared DML lock (Row-X (SX)) caused by the background job.
Since we need to deploy a new release, we need definitely the DDL on another index.
For me it looks strange, that alter index … coalesce cleanup doesn't allow to create another index in parallel. Why should there be any relation?
Question: If we kill the session, will the work of past 2 days be lost and job need to restart again from beginning?
Best Answer
For obvious reasons (laziness) I used the example for creating the table and index from this post:
https://richardfoote.wordpress.com/2013/08/02/12c-asynchronous-global-index-maintenance-part-i-where-are-we-now/
Next drop a partition:
Then analyze the index:
Orphaned entries are presented here as deleted entries.
Here comes my addition. Start the
coalesce cleanup
manually, then interrupt it in a few seconds (Ctrl-C), then analyze the index again:As you can see, even with interrupting the command, some of the entries were cleaned up. Now do it again:
Next create an index then continue:
Finally:
So no, progess was not lost.