oracle+ddl+执行很慢,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

ENABLEapplies 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 asDETERMINISTIC

Restriction on Enabling Function-based IndexesYou cannot specify any other clauses ofALTERINDEXin the same statement withENABLE.

DISABLE Clause

DISABLEapplies 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 anotherALTERINDEXstatement with theENABLEkeyword.

UNUSABLE Clause

SpecifyUNUSABLEto 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 markedUNUSABLE, 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 UnusableYou cannot specify this clause for an index on a temporary table.