ORA-01502錯誤成因和解決方法(一)

　　接到開發人員和業務人員的通知說一個登陸頁面不能用了報錯

　　 :: [mondaoOamUserDAO] :select錯誤
javasqlSQLException: ORA state

　　這個錯誤是由於索引失效造成的重建索引後問題就解決了

　　為了搞清楚索引為什麼會失效以及如何解決我們做個測試

　　首先我們創建一個普通的測試表（非分區表）
SQL> create table t(a number);

　　Table created

　　SQL> select tablespace_name from user_segments where segment_name=T;

　　TABLESPACE_NAME

DATA_DYNAMIC

　　SQL>

　　然後我們創建一個普通索引

　　SQL> create index idxt on t(a);

　　Index created

　　SQL> insert into t values();

　　 row created

　　SQL> set linesize
SQL> select index_nameindex_typetablespace_nametable_typestatus from user_indexes where index_name=IDXT;

　　INDEX_NAME                     INDEX_TYPE                  TABLESPACE_NAME                TABLE_TYPE  STATUS

IDXT                           NORMAL                      DATA_DYNAMIC                   TABLE       VALID

　　SQL>

　　模擬索引是失效的情況
SQL> alter table  t move tablespace tools
    /

　　Table altered

　　SQL> select index_nameindex_typetablespace_nametable_typestatus from user_indexes where index_name=IDXT;

　　INDEX_NAME                     INDEX_TYPE                  TABLESPACE_NAME                TABLE_TYPE  STATUS

IDXT                           NORMAL                      DATA_DYNAMIC                   TABLE       UNUSABLE

　　SQL>
我們看到當使用類似 alter table xxxxxx move tablespace xxxxxxx 命令後索引就會失效

　　當然作為測試也可以直接使用alter index idxt unusable;命令使索引失效例如
SQL> alter index idxt unusable;

　　Index altered

　　SQL>

　　在這種情況下我們向表中插入數據看看是什麼情況
SQL> insert into t values();
insert into t values()
*
ERROR at line :
ORA: index MISCIDXT or partition of such index is in unusable state

　　SQL>
我們看到這時就出現了常見的ORA: index XXXXXXXX or partition of such index is in unusable state錯誤

　　檢查一下索引狀態我們會注意到索引已經是UNUSABLE了
SQL> select index_nameindex_typetablespace_nametable_typestatus from user_indexes where index_name=IDXT;

　　INDEX_NAME                     INDEX_TYPE                  TABLESPACE_NAME                TABLE_TYPE  STATUS

IDXT                           NORMAL                      DATA_DYNAMIC                   TABLE       UNUSABLE

　　SQL>

　　對於普通表中的不同索引（非唯一索引）我們有兩種方法解決這個問題
方法一設置 skip_unusable_indexes=true;
SQL> alter session set skip_unusable_indexes=true;

　　Session altered

　　SQL> insert into t values();

　　 row created

　　SQL> commit;

　　Commit complete

　　SQL> select * from t;

　　A

        
        
        
        
        
       
       

　　 rows selected

　　SQL> select index_nameindex_typetablespace_nametable_typestatus from user_indexes where index_name=IDXT;

　　INDEX_NAME                     INDEX_TYPE                  TABLESPACE_NAME                TABLE_TYPE  STATUS

IDXT                           NORMAL                      DATA_DYNAMIC                   TABLE       UNUSABLE

　　SQL>
現在我們看到這個索引的狀態雖然還是UNUSABLE但是通過設置alter session set skip_unusable_indexes=true;
我們已經可以訪問這個表了但是請注意這種情況下這個索引是不可用的也就是說優化器在考慮是否要使用索引時是不考慮這個所以的

　　方法通過常見所以徹底解決這個問題
首先先設置 skip_unusable_indexes=false也就是不跳過失效索引
SQL> alter session set skip_unusable_indexes=false;

　　Session altered

　　SQL>

　　然後重建這個失效的索引
SQL> alter index idxt rebuild;

　　Index altered

　　SQL> select index_nameindex_typetablespace_nametable_typestatus from user_indexes where index_name=IDXT;

　　INDEX_NAME                     INDEX_TYPE                  TABLESPACE_NAME                TABLE_TYPE  STATUS

IDXT                           NORMAL                      DATA_DYNAMIC                   TABLE       VALID

　　SQL>
我們看到重建索引後索引的狀態就正常了

　　現在插入數據看看是正常
SQL> insert into t values();

　　 row created

　　SQL> commit;

　　Commit complete

　　SQL>
看來重建索引才是解決這類問題的徹底的方法

From:http://tw.wingwit.com/Article/program/Oracle/201311/16930.html