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>
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.