UpdateStats cancelling daily.

[Former Member]

Hi All,

In BW UpdateStats are cancelling daily. with the log :

BR0301E SQL error -20000 at location stats_ind_collect-3, SQL statement:

'BEGIN DBMS_STATS.GATHER_INDEX_STATS (OWNNAME => '"SAPDAT"', INDNAME => '"/BIC/B0000601000KE"', ESTIMATE_PERCENT => NULL, DEGREE => NULL, NO_

ORA-20000: index "SAPDAT"."/BIC/B0000601000KE" or partition of such index is in unusable state

ORA-06512: at "SYS.DBMS_STATS", line 10712

ORA-06512: at "SYS.DBMS_STATS", line 10747

ORA-06512: at line 1

BR0886E Checking/collecting statistics failed for index SAPDAT./BIC/B0000601000KE

Kindly suggest.

Thanks & Regars!!

Ahmed.

[JPReyes]

ORA-20000: index "SAPDAT"."/BIC/B0000601000KE" or partition of such index is in unusable state
ORA-06512: at "SYS.DBMS_STATS", line 10712
ORA-06512: at "SYS.DBMS_STATS", line 10747
ORA-06512: at line 1
BR0886E Checking/collecting statistics failed for index SAPDAT./BIC/B0000601000KE

You can also try to rebuild the index.

Read,

http://help.sap.com/saphelp_nw70/helpdata/en/75/95536a63b44b4a9e7f278c7d9c2b67/content.htm

Regards

Juan

[Former Member]

Check out the following note.

Note 1042182 - Secondary index is inconsistent for PSA tables

This has worked for us

[Former Member]

Hai,

In BW systems you will get this kind of errors when you run Update statistics when there is BI load taking place. Try it running when the system has less traffic.

Check for some metalink notes if the problem persists, you might need to run some scripts to overcome this problem.

Regards,

Yoganand.V

[Former Member]

Hai,

In BW systems you will get this kind of errors when you run Update statistics when there is BI load taking place. Try it running when the system has less traffic.

Check for some metalink notes if the problem persists, you might need to run some scripts to overcome this problem.

What Oracle version you are using, this might a Oracle bug also.

Check SAP Note:948197.

Check Metalink Note:759011.1 which describes the same error.

Use userid and password for Metalink from SAP Note:758563

Regards,

Yoganand.V

Edited by: Yoganand Vedagiri on Mar 25, 2009 12:01 PM

Edited by: Yoganand Vedagiri on Mar 25, 2009 12:05 PM

[Former Member]

Hi,

This is mainly due to Insufficient privileges' for creating statistics.

Log on to Oracle SQL plus as SYSDBA and assign the following rights to the SAPCONN role.

conn /as sysdba

grant analyze any to sapconn;

Regards,

Sachin Rane.

[Former Member]

Hi Syed

please read carefully below links

https://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/10564d5c-cf00-2a10-7b87-c94e3826...

Regards

Bandla

[Former Member]

Hi.

Check is there any corrupted block or index on that particular table.

-Srini