Update statistics ends with error

[Former Member]

Hi Techies

Few months back our BI system got crashed then ,we re-install the system & restore the database,Now the issue is whenever we are running the update statistics it gives the following error & terminates with error.

-

---

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

'BEGIN DBMS_STATS.GATHER_INDEX_STATS (OWNNAME => '"SAPSR3"', INDNAME => '"/BIC/B0000598000KE"', ESTIMATE_PERCENT => NULL, DEGREE => NULL,

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

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

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

ORA-06512: at line 1

BR0886E Checking/collecting statistics failed for index SAPSR3./BIC/B0000598000KE

BR0301E SQL error -20000 at location stats_tab_collect-18, SQL statement:

'BEGIN DBMS_STATS.GATHER_TABLE_STATS (OWNNAME => '"SAPSR3"', TABNAME => '"/BIC/B0000598000"', ESTIMATE_PERCENT => 30, METHOD_OPT => 'FOR A

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

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

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

ORA-06512: at line 1

BR0886E Checking/collecting statistics failed for table SAPSR3./BIC/B0000598000

-

---

Environment Details

OS-AIX 5.3

DB-Oracle 10.2.0.2.0

SAP-SAP Netweaver 2004s

BI-SAP Netweaver BI 7.0

Quick response appreciated

Thanks

Vijay

[former_member227283 · Accepted solution]

Hi,

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

rebuild the index using BRTOOLS.

Run brtools

select 3 --> SEGMENT MANGEMENT --> 2 - Rebuild indexes --> 7 ~ Index names (index)

provide your index name here SAPSR3"."/BIC/B0000598000KE

and rebuild the index

after running rebuild index , start again Update Statistics.

it will run succesful

Thanks

Anil

[Former Member · Accepted solution]

Hi Anil,

I had the same issue when i run DB stats I end up with below error,

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

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

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

ORA-06512: at line 1

I later recreated the index as you mentioned and I was able to run it successfully later

Thanks for all your help

Regards

Uday

[musharaffkhan]

Thank you very much Anil.

I had rebuilt the index as you mentioned and after-that update stats was successfully completed..

Issue resolved.

Regards

Musharaff Khan

[Former Member]

Hi,

You can rebuild the index mentioned in the error message and after that run the db stats and it should be fine. Also, you can do as per the below note and then run the DB stats.

Note 1042182 - Secondary index is inconsistent for PSA tables

Thanks & Regards,

Sharath Babu M