index unusable 확인(index 깨진거 확인 쿼리)

[오류 예제]
ORA-01502: index 'SCOTT.PK_TABLE' or partition of such index is in unusable state

1. 문제가 되는 Index 조회
select index_name, status, owner from dba_indexes where STATUS = 'UNUSABLE';

 

2. 문제가 되는 Index변경 Alter문 생성
select 'alter index '||owner||'.'||index_name||' rebuild;' from dba_indexes where STATUS = 'UNUSABLE';

 

3. 생성된 Alter문 실행
alter index SCOTT.PK_TABLE rebuild;?

--------------------------------------------
일반및 파티션테이블, sup파티션까지 조회
select *
from (
         SELECT 'alter index ' || owner || '.' || index_name || ' rebuild ' || ' nologging parallel 8;'  as cmd
         FROM dba_indexes
         WHERE STATUS = 'UNUSABLE'
         AND OWNER NOT IN ('SYSTEM','SYS')
         union all
         SELECT 'alter index ' || index_owner || '.' || index_name || ' rebuild partition ' || partition_name || ' parallel 8;'
         FROM DBA_IND_PARTITIONS
         WHERE STATUS = 'UNUSABLE'
        union all
        select 'alter index ' || index_name || ' rebuild subpartition ' || subpartition_name || ' parallel 8;'
        from dba_ind_subpartitions
        where status = 'UNUSABLE'
       );