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));</p><p>Table created.</p><p>SQL> create index idx1 on t1(id);</p><p>Index created.</p><p>SQL> create index idx2 on t1(upper(name));</p><p>Index created.</p><p>SQL> alter index idx1 disable;<br />alter index idx1 disable<br />*<br />ERROR at line 1:<br />ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option</p><p>SQL> alter index idx2 disable;</p><p>Index altered.</p><p>SQL> alter index idx1 unusable;</p><p>Index altered.</p><p>SQL> select index_name,status,funcidx_status from user_indexes where index_name<br />in ('IDX1','IDX2');</p><p>INDEX_NAME STATUS FUNCIDX_<br />------------------------------ -------- --------<br />IDX1 UNUSABLE<br />IDX2 VALID DISABLED<br />