Unusable indexes

Unusable indexes

Oracle indexes can go into a UNUSABLE state after maintenance operation on the table or if the index is marked as 'unusable' with an ALTER INDEX command. A direct path

load against a table or partition will also leave its indexes unusable.
Queries and other operations agains a table with unuable indexes will generate errors:


ORA-01502: index ‘string.string’ or partition of such index is in unusable state

#################################################################################
unusable indexes
#################################################################################
select OWNER,OBJECT_TYPE,status,count(*) from dba_objects where STATUS='!VALID' group by OWNER,OBJECT_TYPE,status;
select OWNER,OBJECT_TYPE,status,count(*) from dba_objects where status='INVALID' group by OWNER,OBJECT_TYPE,status;
SELECT owner,index_name,TABLE_OWNER,TABLE_NAME,PARTITIONED,TABLESPACE_NAME,STATUS
 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'
       )
      )
   )
  );

Select 'ALTER INDEX '|| index_name ||' rebuild partition ' || PARTITION_NAME ||';' from USER_IND_PARTITIONS;
Select 'ALTER INDEX '|| index_name ||' rebuild partition ' || PARTITION_NAME || ' TABLESPACE ' || tablespace_name ||';' from USER_IND_PARTITIONS;
Select 'ALTER INDEX '||INDEX_OWNER||'.'|| index_name ||' rebuild partition ' || PARTITION_NAME || ' TABLESPACE ' || tablespace_name ||';' from DBA_IND_PARTITIONS
where INDEX_OWNER='ERXUATOWNER' and status='UNUSABLE';
Select 'ALTER INDEX '||INDEX_OWNER||'.'|| index_name ||' rebuild partition ' || PARTITION_NAME || ' TABLESPACE ' || tablespace_name ||';' from DBA_IND_PARTITIONS
where INDEX_OWNER='ERXUATOWNER' and status='UNUSABLE';

#################################################################################
The following SQL comands can be used to detect unusable indexes:

Indexes:
SELECT owner, index_name, tablespace_name
FROM   dba_indexes
WHERE  status = 'UNUSABLE';

Index partitions:

SELECT index_owner, index_name, partition_name, tablespace_name
FROM   dba_ind_PARTITIONS WHERE  status = 'UNUSABLE';


The following SQL will print out a list of alter commands that can be executed to fix unusable indexes:

Indexes:

SELECT 'alter index '||index_name||' rebuild tablespace '||tablespace_name ||';'
FROM   dba_indexes WHERE  status = 'UNUSABLE';

Index partitions:

SELECT 'alter index '||index_name ||' rebuild partition '||PARTITION_NAME||' TABLESPACE '||tablespace_name ||';' FROM   dba_ind_partitions WHERE  status = 'UNUSABLE';