How to see the status of all indexes; enabled/disabled in Oracle database?-CodePudding

I can't seem to find the way for querying user_indexes table for their status. I guess there is no concept of an index to be enabled or disabled.

CodePudding user response：

Seems as if you didn't look close enough.

SQL> select index_name, status from user_indexes;

INDEX_NAME                     STATUS
------------------------------ --------
PK_EMP                         VALID
PK_DEPT                        VALID
<snip>

As of "disabled" indexes - generally speaking, you can't do that:

SQL> alter index pk_dept disable;
alter index pk_dept disable
*
ERROR at line 1:
ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option

What you can do is to make it unusable:

SQL> alter index pk_dept unusable;

Index altered.

SQL> select index_name, status from user_indexes;

INDEX_NAME                     STATUS
------------------------------ --------
PK_DEPT                        UNUSABLE
<snip>

To make it "usable" again, you have to rebuild it:

SQL> alter index pk_dept rebuild;

Index altered.

SQL> select index_name, status from user_indexes;

INDEX_NAME                     STATUS
------------------------------ --------
PK_DEPT                        VALID
<snip>

Non-generally speaking, you can disable function-based index:

SQL> create index myind on emp (to_char(hiredate, 'yyyymm'));

Index created.

SQL> alter index myind disable;

Index altered.

SQL>

CodePudding user response：

You are not very precise in your formulation what do you meen with enable/disable.

Anyway if you want to disable or enable the index from the usage by the Oracle optimizer use

alter index index_name invisible;
alter index index_name visible;

It is a good practice while adding a new index to create it as invisible, and only after a test in a session that can use it

alter session set optimizer_use_invisible_indexes=true;

make it visible to generall usage.

Contrary to popular believe not only a missing index but also added index can ruin the performance.