Listing invalid indexes in Oracle

all_indexesdba_indexesuser_indexesVALIDINVALIDN/AN/A

WHERE status != 'VALID'

N/Aall_indexesall_ind_partitionsdba_ind_partitionsuser_ind_partitionsUSABLEUNUSABLEN/A

statusN/A

all_ind_subpartitionsuser_ind_subpartitions and dba_ind_subpartitions. Again, we have a status column. This time, though, we can only have two possible values, USABLE, and UNUSABLE. At this point, we can determine if our index is valid or not by looking at its partition's subpartition's status.

Okay, so this is all well and good, but what we really need is a query that will give us the name of indexes that need rebuilding. Global indexes do not have partitions, and not all partitioned indexes have subpartitions, yet we want to know all invalid indexes, regardless of their makeup. The following query accomplishes this.

select

index_name

from

all_indexes

where

owner not in ('SYS', 'SYSTEM')

and

status != 'VALID'

and (

status != 'N/A'

or

index_name in (

  select

    index_name

  from

    all_ind_partitions

  where

    status != 'USABLE'

    and (

      status != 'N/A'

      or

      index_name in (

        select

          index_name

        from

          all_ind_subpartitions

        where

          status != 'USABLE'

      )

    )

)

);

I'll leave dissecting how this query works as a lesson to the reader. Here I am also leaving out the indexes owned by SYS and SYSTEM, but you can remove that line if you need those indexes listed as well.

I do presently have an anonymous block that makes a cursor out of this query, loops through it, and builds all indexes, partitioned indexes, and subpartitioned indexes, as is appropriate. I am still working on making sure that it is battle hardened. After that I'll post about how I accomplish this. Combined they provide an elegant solution to finding all of your invalid indexes and rebuild them.

If you find this helpful, or have some additional information to provide, please do so in the comment section below.

Edit: The script is available at Rebuilding invalid indexes in Oracle Posted by

Jack David Baucum

at

[](http://maxolasersquad.blogspot.com/2010/09/listing-invalid-partitions-in-oracle.html)

[

](https://www.blogger.com/email-post.g?blogID=2160921736769720124&postID=2381303283611064231)

Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest
+7 在 Google 上推荐

—

Labels: PL/SQL, Programming, technology