Find Invalid,Unusable Indexes.Rebuild Index. 找出失敗、無法使用的索引

此篇為翻譯文章

資料來源如下：

http://waseemslearning.blogspot.tw/2011_06_02_archive.html

Check the Status of Indexes:  

確認索引的狀態

SELECT status,count(*) from USER_INDEXES GROUP BY STATUS;

SELECT status,count(*) from ALL_INDEXES GROUP BY STATUS;

SELECT status,count(*) from DBA_INDEXES GROUP BY STATUS;

Solution

   1. Drop the specified index and/or recreate the index

   2. Rebuild the specified index

   3. Rebuild the unusable index partition

解決方案

   1.刪除特定索引，然後重建它

   2.重建特定索引

   3.重建不可使用的索引區塊

Rebuilding UNUSABLE indexes online, querying to USER_INDEXES,ALL_INDEXES,DBA_INDEXES view.

創造出重建語法

Sql>  SELECT count(*),status FROM all_indexes GROUP BY status;

Sql>  select 'alter index '||owner||'.'||index_name||' rebuild online ;' from dba_indexes 
          where status = 'UNUSABLE' ;

Output :
輸出如下：
 'ALTERINDEX'||OWNER||'.'||INDEX_NAME||'REBUILDONLINE;'
--------------------------------------------------------------------------------
alter index APPLSYS.FND_COLUMNS_U4 rebuild online ;
alter index APPLSYS.FND_COLUMNS_U2 rebuild online ;
alter index APPLSYS.WF_PROCESS_ACTIVITIES_U2 rebuild online ;
alter index APPLSYS.WF_PROCESS_ACTIVITIES_PK rebuild online ;
alter index APPLSYS.WF_PROCESS_ACTIVITIES_N2 rebuild online ;
alter index APPLSYS.WF_PROCESS_ACTIVITIES_N1 rebuild online ;
alter index APPLSYS.FND_COLUMNS_U3 rebuild online ;
alter index APPLSYS.FND_COLUMNS_U1 rebuild online ;