Oracle 執行 DDL 長時間無響應

Oracle資料庫上遇到一個很無語的問題。同事告訴我，不能刪除某個表的某個索引，其他操作都沒問題。看了一下，這個表只有幾千條資料，上面有2個組合唯一索引。刪除其中的一個唯一索引

SQL> drop index IDX_U_STDARD;

執行後，長時間無響應，不返回任何資訊，感覺是被鎖了，可查詢v$locked_object檢視，沒看到被鎖定的物件。（不清楚是否還有其他可藉助的檢視）

既然不能刪，看能不能disable掉

SQL> alter index IDX_U_STDARD disable;

還是一樣，長時間無響應。

如是，想到rebuild，這次執行很長時間後，居然成功了。

SQL> alter index IDX_U_STDARD rebuild; 

 

Index altered.

 

太不容易了，再次disable看看。

SQL> alter index IDX_U_STDARD disable;

alter index IDX_U_STDARD disable

*

ERROR at line 1:

ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option

 

終於看到錯誤提示了，離成功應該也就不遠了。馬上查詢該錯誤原因，原來disable 和 enable只針對函式索引有效，一般索引推薦unusable。

SQL> alter index IDX_U_STDARD unusable;

 

Index altered.

 

索引被設定為無用後，就可以刪除了。

SQL> drop index IDX_U_STDARD;

 

Index dropped.

對於該表上的另一個唯一索引，直接drop掉可以。

 

下面是找到的關於enable和diable的簡單使用說明。

 

ENABLE Clause

ENABLE applies only to a function-based index that has been disabled because a user-defined function used by the index was dropped or replaced. This clause enables such an index if these conditions are true:

·         The function is currently valid

·         The signature of the current function matches the signature of the function when the index was created

·         The function is currently marked as DETERMINISTIC

Restriction on Enabling Function-based Indexes You cannot specify any other clauses of ALTER INDEX in the same statement with ENABLE.

DISABLE Clause

DISABLE applies only to a function-based index. This clause lets you disable the use of a function-based index. You might want to do so, for example, while working on the body of the function. Afterward you can either rebuild the index or specify another ALTER INDEX statement with the ENABLE keyword.

UNUSABLE Clause

Specify UNUSABLE to mark the index or index partition(s) or index subpartition(s) UNUSABLE. An unusable index must be rebuilt, or dropped and re-created, before it can be used. While one partition is marked UNUSABLE, the other partitions of the index are still valid. You can execute statements that require the index if the statements do not access the unusable partition. You can also split or rename the unusable partition before rebuilding it.

Restriction on Marking Indexes Unusable You cannot specify this clause for an index on a temporary table.