Thursday, November 5, 2015

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,
       'ALTER index ' || owner || '.' || index_name || ' REBUILD;' stmt
  FROM (SELECT owner,
               NULL partition_name,
          FROM all_indexes --Non Partitioned tables
         WHERE status NOT IN ('VALID', 'N/A')
        SELECT owner,
          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.

No comments:

Post a Comment