The Database Administrator can repair unusable indexes by rebuilding them using the command:

ALTER INDEX … REBUILD

This command creates a new index that points to the correct location of the table. When the new index is created, the old unusable index is deleted.

alter index cno_user rebuild;

There are several options when rebuilding indexes using the REBUILD command:

1- Tablespace: The index is created in the current Tablespace, but it can be moved to another Tablespace by specifying the Tablespace where the index will be created.

2- ONLINE: During the index creation on the table, the table is originally locked (LOCK) against DML operations, but this lock can be avoided by using the (ONLINE) keyword.

3- NOLOGGING: With this option, Oracle will not generate Redo during index creation, meaning the creation process will be faster. However, a backup must be performed immediately for the Tablespace in which the index was created.