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.

-

---

BR0280I BRCONNECT time stamp: 2010-02-16 06.47.19

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

BR0280I BRCONNECT time stamp: 2010-02-16 06.47.19

BR0883I Table selected to collect statistics after check: SAPSR3./BIC/B0000598000 (856/35464:0:0)

BR0280I BRCONNECT time stamp: 2010-02-16 06.47.19

BR0881I Collecting statistics for table SAPSR3./BIC/B0000598000 with method/sample E/P30 ...

BR0280I BRCONNECT time stamp: 2010-02-16 06.47.19

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

BR0280I BRCONNECT time stamp: 2010-02-16 06.47.19

BR0882I Collecting statistics for index SAPSR3./BIC/B0000612000KE with method/sample E/P30 ...

BR0280I BRCONNECT time stamp: 2010-02-16 06.47.19

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

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

ORA-20000: index "SAPSR3"."/BIC/B0000612000KE" 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/B0000612000KE

BR0280I BRCONNECT time stamp: 2010-02-16 06.47.19

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

Warm Regards

Vijay

Edited by: Vijay Pagdhare on Feb 16, 2010 12:25 PM

[former_member204746 · Accepted solution]

Please read the following SAP notes:

761745

838725

[former_member524429]

Hi,

Post the output of the following SQL Statement:

select INDEX_NAME,PARTITION_NAME,STATUS from DBA_IND_PARTITIONS where INDEX_NAME='/BIC/B0000598000KE';

You can drop the indexes and re-create the same, about which the failure log is complaining.

Also refer this [reference link|http://mysapbasis.wordpress.com/2009/07/18/ora-20000/] to get more info.

Regards,

Bhavik G. Shroff