ORA-02243: ALTER INDEX 或 ALTER MATERIALIZED VIEW 选项无效

早上论坛里一个朋友在想使索引无效的时候遇到了如下错误：

ORA-02243: ALTER INDEX 或 ALTER MATERIALIZED VIEW 选项无效
他使用的语句如下：

ALTER INDEX index_name DISABLE;

 

刚看到的时候我以为是由于约束存在，所以无法DISABLE索引，因为记得以前学习主键的过程中有个印象，删除唯一索引时，若存在对应的主键约束，则不能删除。相关的学习贴如下

http://blog.csdn.net/wh62592855/archive/2009/10/24/4724232.aspx

 

可是后来看了其他朋友的回答，去翻了翻文档，好像并不是由于这个原因。下面从文档中摘录一段内容：

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.

可以看到，DISABLE和ENABLE只针对函数索引有效，对于其他索引不行的。你可以选择使用UNUSABLE。

下面做点实验来看看

SQL> create table t1(id number,name varchar2(20));Table created.SQL> create index idx1 on t1(id);Index created.SQL> create index idx2 on t1(upper(name));Index created.SQL> alter index idx1 disable;alter index idx1 disable*ERROR at line 1:ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW optionSQL> alter index idx2 disable;Index altered.SQL> alter index idx1 unusable;Index altered.SQL> select index_name,status,funcidx_status from user_indexes where index_namein ('IDX1','IDX2');INDEX_NAME STATUS FUNCIDX_------------------------------ -------- --------IDX1 UNUSABLEIDX2 VALID DISABLED