Error in Update optimizer statistics - index is in unusable state

[Former Member]

Hello,

we have this error in log Check and update optimizer statistics:

12.02.2009 23:21:20 'BEGIN DBMS_STATS.GATHER_TABLE_STATS (OWNNAME => '"SAPPB1"', TABNAME => '"/BIC/FZPPC0002"', ESTIMATE_PERCENT => 10, METHOD_OPT =

12.02.2009 23:21:20 ORA-20000: index "SAPPB1"."/BIC/FZPPC0002~010" or partition of such index is in unusable state

12.02.2009 23:21:20 ORA-06512: at "SYS.DBMS_STATS", line 13452

12.02.2009 23:21:20 ORA-06512: at "SYS.DBMS_STATS", line 13472

12.02.2009 23:21:20 ORA-06512: at line 1

12.02.2009 23:21:20 BR0886E Checking/collecting statistics failed for table SAPPB1./BIC/FZPPC0002

i can temporary fix this problem when i delete and recreate index via SE14, but this help only for one next running update statistics, every next running update statistics has same error: index "SAPPB1"."/BIC/FZPPC0002~010" or partition of such index is in unusable state. Exist any definitive solution for this problem. Thanks

[Former Member]

Hi,

Two methods for checking/repairing Indexing issues

1)RSRV for a particular cube

2)SAP_INFOCUBE_INDEXES_REPAIR report

You can also use this sql to rebuild an Index.

alter index <index name> rebuild online; Or you can rebuild Index by using ABAP report 'RSANAORA'.

Please check below thread it may help you.

/message/6483705#6483705 [original link is broken]

https://forums.sdn.sap.com/click.jspa?searchID=12942068&messageID=2052264

Thanks,

Sushil

[donald_voorhees]

This is quite common, do an onlne rebuild of the index at the Oracle level, it should work after that.

[lbreddemann]

Hi there,

> 12.02.2009 23:21:20 ORA-20000: index "SAPPB1"."/BIC/FZPPC0002~010" or partition of such index is in unusable state

> i can temporary fix this problem when i delete and recreate index via SE14, but this help only for one next running update statistics, every next running update statistics has same error: index "SAPPB1"."/BIC/FZPPC0002~010" or partition of such index is in unusable state. Exist any definitive solution for this problem. Thanks

actually you should ask "why has on partition of the index become UNUSABLE?".

Usually this happens, when there had been actions to the table which somehow change or invalidate rowids. These actions include TRUNCATE TABLE, DROP PARTITION, ALTER TABLE ... MOVE, ALTER TABLE ... SHRINK etc.

As this is a index of a F-facttable, I would suspect that one of your loading or compressing process chains does not correctly rebuild the index after it has done its job.

regards,

Lars

[Former Member]

Hi,

Attached link will help you solve the problem.

[;

Hope this helps.

Manoj

[Former Member]

Hello,

Two SAP notes you should check:

- 948197 (> requires installing an interim patch for Oracle 10.2.0.2)

- 1042182

Regards,

Mark