Drop Indexes Online Without ORA-0054 Error

Prior to the Oracle 12c version, droping an index was an offline process that put a lock on the original table in exclusive mode. Exclusive lock is not required in the index drop operation since version 12C. Therefore, this process can now be performed online with 12c.

In Oracle 11g, if a table has a lock in the Exclusive mode (mode = 3), you can not drop the index and you will receive the below error.

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

With 12c this error has been eliminated.

An index can be dropped online in 12c databases as below.

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *