Rebuild unusable Index

The below is the query to rebuild unusable Partitioned and unpartitioned Indexes.

Run the 'Alter index' script when there are unusable indexes.

--Query to rebuild unusable Partitioned and Non Partitioned Indexes

SELECT owner,

       index_name,

       'ALTER index ' || owner || '.' || index_name || ' REBUILD;' stmt

  FROM (SELECT owner,

               table_name,

               index_name,

               NULL partition_name,

               index_type,

               blevel,

               leaf_blocks,

               distinct_keys,

               num_rows

          FROM all_indexes --Non Partitioned tables

         WHERE status NOT IN ('VALID', 'N/A')

        UNION

        SELECT owner,

               table_name,

               i.index_name,

               partition_name,

               index_type,

               ip.blevel,

               ip.leaf_blocks,

               ip.distinct_keys,

               ip.num_rows

          FROM all_indexes i, all_ind_partitions ip --Partitioned Tables

         WHERE     i.owner = ip.index_owner

               AND i.index_name = ip.index_name

               AND ip.status != 'USABLE');

If there are many database actions involved everyday, then we can think of creating a database job for this and running this every day after business hours.