ORA-20000: index "FCSASUSER"."IDX_DN_TREE_ID" or partition of such index is in unusable state

alert日志发现如下报错：

Sat Jul 30 06:00:12 CST 2016
Errors in file /u01/app/oracle/admin/crfdw/bdump/crfdw_j001_17225.trc:
ORA-20000: index "FCSASUSER"."IDX_DN_TREE_ID"  or partition of such index is in unusable state

SQL> select owner, index_name from dba_indexes where status='UNUSABLE';
OWNER                          INDEX_NAME
------------------------------ ------------------------------
FCSASUSER                      IDX_DN_NODE_NO
FCSASUSER                      IDX_DN_TREE_ID
SQL> ALTER INDEX FCSASUSER.IDX_DN_TREE_ID REBUILD;
Index altered.
SQL> 

Gather Schema Statistics Completed ERR ORA-20000 Index in unusable state (文档 ID 373488.1)

转到底部

---

In this Document Symptoms   Cause   Solution APPLIES TO: Oracle Order Management - Version 11.5.10.0 to 11.5.10.3 [Release 11.5.10] Oracle Assets - Version 12.1.3 to 12.1.3 [Release 12.1] Information in this document applies to any platform. ***Checked for relevance on 25-MAR-2013*** SYMPTOMS When attempting to run Gather Schema Statistics, the following error occurs. FNDGSCST module: Gather Schema Statistics ... In GATHER_SCHEMA_STATS , schema_name= ONT percent= 10 degree = 16 internal_flag= NOBACKUP Error #1: ERROR: While GATHER_TABLE_STATS: object_name=ONT.OE_ORDER_LINES_HISTORY ***ORA-20000: index "ONT"."OE_ORDER_LINES_HISTORY_N2" or partition of such index is in unusable state*** CAUSE The issue is caused by invalid OE_ORDER_LINES_HISTORY_N4 index SOLUTION To implement the solution, please execute the following steps: 1. Run query to find the list of indexes which are in unusable status : select owner, index_name, index_type,  table_owner, table_name, tablespace_name,  status, last_analyzed from dba_indexes where status='UNUSABLE'; 2. Based on the query output, rebuild the indexes, using command : ALTER INDEX <index_name> REBUILD; 3. Please re-run gather schema statistics. 4. If the issue is resolved, please migrate the solution as appropriate to other environments.