ORA-02243: the alter index or alter materialized view option is invalid

In the morning, a friend in the forum encountered the following error when trying to invalidate the index:

ORA-02243: the alter index or alter materialized view option is invalid
The statement used is as follows:

Alter index index_name disable;

 

When I first saw it, I thought it was because of constraints, so I could not disable the index, because I remember that I had an impression in the previous primary key learning process. When deleting a unique index, if there is a corresponding primary key constraint, it cannot be deleted. The related learning stickers are as follows:

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

 

However, after reading the answers from other friends, I flipped through the document. It seems that this is not the reason. The following is an excerpt from the document:

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 markedDETERMINISTIC

Restriction on enabling function-based indexes you cannot specify any other clsesALTER INDEXIn the same statementENABLE.

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 anotherALTER INDEXStatement withENABLEKeyword.

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 unusable you cannot specify this clause for an index on a temporary table.

As you can see, disable and enable are only valid for function indexes, but not for other indexes. You can choose to use unusable.

Let's take a look at the experiment below.

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/>