Sample Header Ad - 728x90

Does interrupting ALTER INDEX COALESCE CLEANUP lose the work so far?

2 votes
1 answer
3566 views
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](https://docs.oracle.com/database/121/VLDBG/GUID-087B87A6-959A-40C6-82AF-36E401FD089B.htm#VLDBG14107) 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?
Asked by D. Lohrsträter (180 rep)
Apr 21, 2020, 09:40 AM
Last activity: Apr 21, 2020, 09:10 PM