今天移动表到另一个表空间出现 :ORA-01502 Index state unusable

今天无意中看到有几个业务表在system表空间，看着不爽，就移动了这些表到业务表空间。

alter table aplm.T_BG_CUST_VISIT_REPORT_D move  tablespace  APLM_DATA;                                                                                                                                  
alter table aplm.T_BG_CUST_VISIT_REPORT_FILE_D move  tablespace  APLM_DATA;                                                                                                                             
alter table aplm.T_BG_CUST_VISIT_REPORT_FILE_M move  tablespace  APLM_DATA;                                                                                                                             
alter table aplm.T_BG_CUST_VISIT_REPORT_M move  tablespace  APLM_DATA;                                                                                                                                  
alter table aplm.T_CL_PRODUCT_TRACE_D move  tablespace  APLM_DATA;                                                                                                                                      
alter table aplm.T_CL_PRODUCT_TRACE_IMPORT move  tablespace  APLM_DATA;                                                                                                                                 
alter table aplm.T_CL_PRODUCT_TRACE_M move  tablespace  APLM_DATA;                                                                                                                                      
alter table aplm.T_DH_ORDER_ANALYSIS move  tablespace  APLM_DATA;                                                                                                                                       
alter table aplm.T_DH_ORDER_PARTS_ANALYSIS move  tablespace  APLM_DATA;                                                                                                                                 
alter table aplm.T_DH_ORDER_PARTS_IMPORT move  tablespace  APLM_DATA;                                                                                                                                   
alter table aplm.T_JC_PRODUCT_TYPE move  tablespace  APLM_DATA;                                                                                                                                         
alter table aplm.T_JC_PRODUCT_TYPE_SHOPSIGN move  tablespace  APLM_DATA;                                                                                                                                
alter table aplm.T_JC_PRODUCT_TYPE_STEEL move  tablespace  APLM_DATA;                                                                                                                                   
alter table aplm.T_LBJ_PART_IMAGE move  tablespace  APLM_DATA;                                                                                                                                          
alter table aplm.T_LBJ_PART_MGR_IMPORT move  tablespace  APLM_DATA;                                                                                                                                     
alter table aplm.T_SS_CODE_VALUE move  tablespace  APLM_DATA;                                                                                                                                           
alter table aplm.T_SS_CUSTOMER move  tablespace  APLM_DATA;                                                                                                                                             
alter table aplm.T_SS_PERS move  tablespace  APLM_DATA;                                                                                                                                                 
alter table aplm.T_XS_SALES_TRACE move  tablespace  APLM_DATA;                                                                                                                                          
alter table aplm.T_BS_EPS_USERS move  tablespace  APLM_DATA; 

移动完以后，用户插入数据的时候报

ORA-01502: index 'XXXXXXXX' or partition of such index is in unusable state”错误。

 

查看

 select  *   from user_indexes where status = 'UNUSABLE';

发现里面有好多'UNUSABLE'状态的索引

解决办法：

alter session set skip_unusable_indexes=false;

alter index xxxxx rebuild;

重建所有索引；

问题解决

链接： http://space.itpub.net/7551038/viewspace-613707