Re-build the failed index and the invalid Index

Re-build the failed index and the invalid Index

If an index is invalid in the database, a large number of full table scans occur for the operations on the table where the index is located because of the index. This consumes a lot of resources, therefore, these invalid indexes must be reconstructed online. Follow these steps to recreate an invalid index:

1. check whether there are any invalid indexes in the database.

Select owner, table_name, index_name, status, last_analyzed from dba_indexes where status = 'unsable ';

2. If an invalid index exists, it must be rebuilt online.

Alter index mw_app.ind_t_id rebuild online;

3. Check whether the index is rebuilt.

Note: If the index status is 'valid', the index has been rebuilt.

Select owner, table_name, index_name, status, last_analyzed from dba_indexes where index_name = 'ind _ T_ID ';

4. query the database again for any invalid Index

Note: The query has no records, indicating that the database has no invalid indexes. At this point, all invalid indexes have been rebuilt.

Select owner, table_name, index_name, status, last_analyzed from dba_indexes where status = 'unsable ';