Oracle makes index unavailable:
Alter index index_name unusable;
After successful execution, if you need to use the index later, you must rebuild it. It will automatically become usable when rebuilt.
According to Oracle's official documentation (an unusable index must is rebulit, or dropped and re-created, before it can be used.)
There are two ways of rebuilding
1. Rebuild
Alter index INDEX_NAME rebuild;
2. Drop the index and rebuild it.
Drop index index_name;
CREATE index index_name on xxxxx;
In fact, the results of the two operations are the same, are deleted and then re-enabled, but the Rebulid method is faster and simpler.
In addition, the database also has two kinds of statements to modify the index state, called Disable and enable; 1. Enable index
Alter index index_name enable;
2. Disable Index
Alter index index_name disable;
The difference is: Enable and disable are only for function indexes.
Oracle's official documentation provides the following statement:
ENABLE Clause
Enable applies only to a function-based index this has been disabled because a user-defined function used by the index is Dropped or replaced. This clause enables such a index if these conditions are true:
The function is currently valid
The signature of the current function matches the signature's function when the index is created
The function is currently marked asdeterministic
Restriction on enabling function-based Indexes You cannot specify no other clauses of ALTER INDEX in the same statement W ITH ENABLE.
DISABLE Clause
DISABLE applies only to a function-based index. This clause lets your disable the use of a function-based index. You are might want to does so, for example, while working on the body of the function. Afterward 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 is 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. Can execute statements that require the index if the statements does not access the unusable partition. You can also split or rename the unusable partition before rebuilding it.
Restriction on marking Indexes unusable your cannot specify this clause to a temporary table.
If you find that an index is invalidated, use the Enable command for it, which may cause ORA-02243 errors, since enable is only valid for function indexes, you can try rebuild, and you may encounter this error if you perform an expiration command on an index, for the same reason.
So, you can change your command.