what are the oracle index status

Oracle index is a database object that is used to improve the performance of certain queries by providing direct access to the data in a table.

Indexes are created on columns and contain entries that point to the data in the table.

An index can be unique or non–unique and can be created on one or more columns. some of status are below.

1.Unusable

2.N/A
3.Valid

4.Invalid

1.UNUSABLEAn Oracle index can become unusable if the underlying table has been modified or dropped, or if the index has been corrupted. To fix this, you need to re–create the index.The data is not in the order specified by the SORTED INDEXES clause

ALTER INDEX [schema.]indexname REBUILD ONLINE;

Drop Index [schema.]indexname;

if the index is still unable to drop,rebuild or it might say resource busy while in unusable state, can try below command and wait to object become available, need to specify timeout how long to should wait.

alter session set ddl_lock_timeout = 600;

Still issue occures need to bring in invisibe mode and try to drop the index

alter index index_name invisible;

2. N/A
N/A indicates that index is a partitioned index, not applicable for User_indexes and dba_indexes need to view with user_ind_partitions and dba_ind_partitions.

3.Valid
The index is in Valid state means it ready to point data in the table.

4,INVALID
An Oracle index can become invalid if it references a table or column that has been dropped, or if the index has been corrupted.
It can also become invalid if the structure of the table has been altered in such a way that the index is no longer valid.
An index can also become invalid if it is not maintained after certain operations, such as an import or export.

alter index index name rebuild online.

Marimuthu Pandu