数据库巡检时发现数据库在搜集统计信息时发现有索引unusable，

Tue Sep 09 22:52:07 CST 2014

GATHER_STATS_JOB encountered errors.  Check the trace file.

Tue Sep 09 22:52:07 CST 2014

Errors in file /oracle/product/10.2/db_1/admin/dcods/bdump/dcods1_j003_9961660.trc:

ORA-20000: index "OMCW_OWNER"."PK_EQ_EXCP_OVERVIEW"  or partition of such index is in unusable state

 

haods1:/home/oracle$more /oracle/product/10.2/db_1/admin/dcods/bdump/dcods1_j003_9961660.trc

 

/oracle/product/10.2/db_1/admin/dcods/bdump/dcods1_j003_9961660.trc

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

ORACLE_HOME = /oracle/product/10.2/db_1

System name:    AIX

Node name:      haods1

Release:        1

Version:        6

Machine:        00F773F14C00

Instance name: dcods1

Redo thread mounted by this instance: 1

Oracle process number: 97

Unix process pid: 9961660, image: oracle@haods1 (J003)

 

*** 2014-09-09 22:52:07.689

*** ACTION NAME:(GATHER_STATS_JOB) 2014-09-09 22:52:07.684

*** MODULE NAME:(DBMS_SCHEDULER) 2014-09-09 22:52:07.684

*** SERVICE NAME:(SYS$USERS) 2014-09-09 22:52:07.684

*** SESSION ID:(2374.62337) 2014-09-09 22:52:07.684

ORA-20000: index "OMCW_OWNER"."PK_EQ_EXCP_OVERVIEW"  or partition of such index is in unusable state

*** 2014-09-09 22:52:07.689

GATHER_STATS_JOB: GATHER_TABLE_STATS('"OMCW_OWNER"','"EQ_EXCP_OVERVIEW"','""', ...)

ORA-20000: index "OMCW_OWNER"."PK_EQ_EXCP_OVERVIEW"  or partition of such index is in unusable state

在dba_indexes里发现此索引确实是”UNUSABLE"

解决办法：alter index OMCW_OWNER.PK_EQ_EXCP_OVERVIEW rebuild online;

使用online选项，允许在重建索引的时候更新基础表。