Gathering Schema Stats ORA-20000: Index "FA"."FA_RX_LOV_U1" or Partition of Such Index Is Unusable
Symptoms:
When running Gathering Schema Statistics
The following error is returned..
ORA-20000: index "FA"."FA_RX_LOV_U1" or partition of such index is in unusab
SQL> EXEC FND_STATS.gather_schema_statistics (schemaname=>'ALL', estimate_percent=>dbms_stats.auto_sample_size, options=>'GATHER AUTO');
BEGIN FND_STATS.gather_schema_statistics (schemaname=>'ALL', estimate_percent=>dbms_stats.auto_sample_size, options=>'GATHER AUTO'); END;
*
ERROR at line 1:
ORA-20000: index "FA"."FA_RX_LOV_U1" or partition of such index is in unusable
state
ORA-06512: at "SYS.DBMS_STATS", line 26211
ORA-06512: at line 1
ORA-06512: at "APPS.FND_STATS", line 711
ORA-06512: at "APPS.FND_STATS", line 1836
ORA-06512: at "APPS.FND_STATS", line 1183
ORA-06512: at "APPS.FND_STATS", line 1275
ORA-06512: at "APPS.FND_STATS", line 815
ORA-06512: at line 1
The issue can be reproduced with Running command to gather schema stats for "ALL"
Cause:
The index is unusable.
The following select statements confirm this:
SQL> select owner, index_name, status, last_analyzed from dba_indexes where index_name = 'FA_RX_LOV_U1';
SQL> select index_owner, index_name, status, last_analyzed from dba_ind_partitions where index_name = 'FA_RX_LOV_U1';
Results from 1st SQL above.
OWNER INDEX_NAME STATUS LAST_ANALYZED
-------- -------------------- -------------- ------------------
FA FA_RX_LOV_U1 UNUSABLE
Rows from 2nd SQL above.
No rows returned.
Solution:
1) Rebuild the index using the following command.
SQL> alter index fa.FA_RX_LOV_U1 rebuild online;
2) Re-run the gathering of schema statistics
Reference metalink Doc ID 2013352.1
No comments:
Post a Comment