Find all index that needs to be rebuilt

Question

I have been searching for a way to find all the indices that needs to be re-built. But couldn't find a syntax on the web.

Is there a syntax to find the same?

Answer 1

It is unusable. Have a look at the following example:

SQL> create table test (id number);

Table created.

SQL> create index i1t on test (id);

Index created.

Make the index unusable:

SQL> alter index i1t unusable;

Index altered.

SQL> select index_name from user_indexes where status = 'UNUSABLE';

INDEX_NAME
------------------------------
I1T

Rebuild it and check its status again:

SQL> alter index i1t rebuild;

Index altered.

SQL> select index_name from user_indexes where status = 'UNUSABLE';

no rows selected

SQL>

---

Of course, you wouldn't rebuild them manually - write a script to do it for you. For example:

SQL> set serveroutput on;
SQL> begin
  2    for cur_r in (select index_name from user_indexes
  3                  where status = 'UNUSABLE'
  4                 )
  5    loop
  6      dbms_output.put_line('Rebuilding index ' || cur_r.index_name);
  7      execute immediate 'alter index ' || cur_r.index_name || ' rebuild';
  8    end loop;
  9  end;
 10  /
Rebuilding index I2T
Rebuilding index I1T

PL/SQL procedure successfully completed.

SQL>