Update statistics error

[Former Member]

Hi all...

I am getting error while performing the update statistics (DB13) in the system.

The following is the error log .

BR0280I BRCONNECT time stamp: 2009-04-15 01.34.59

BR0881I Collecting statistics for table SAPSR3./BIC/B0000117000 with method/sample C ...

BR0280I BRCONNECT time stamp: 2009-04-15 01.34.59

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

'BEGIN DBMS_STATS.GATHER_TABLE_STATS (OWNNAME => '"SAPSR3"', TABNAME => '"/BIC/B0000117000"', ESTIMATE_PERCENT => NULL, METHOD_O

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

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

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

ORA-06512: at line 1

BR0886E Checking/collecting statistics failed fortable SAPSR3./BIC/B0000117000

BR0280I BRCONNECT time stamp: 2009-04-15 01.34.59

BR0883I Table selected to collect statistics after check: SAPSR3./BIC/B0000118000 (66/15:0:0)

BR0280I BRCONNECT time stamp: 2009-04-15 01.34.59

BR0881I Collecting statistics for table SAPSR3./BIC/B0000118000 with method/sample C ...

BR0280I BRCONNECT time stamp: 2009-04-15 01.34.59

Please help me to know what is happening and how to resolve the issue.

Thanks and regards,

Bhuvaneswari

[Former Member]

Hi,

Canyou try by rebuilding index online either by sql or by using report RSANAORA at SAP level.

Please have a look at below thread. It may help you.

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

Thanks,

Sushil

[Former Member]

Hello

Since this requires rebuilding of unpartitioned index (if want to do only rebuilding of index not other errors getting in updatestats here in this post)

at sqplprompt

first we have to get the partition name related to affected index

select index_name, partition_name from dba_ind_partitions where index_name = '<affected_index_name>';

then we have to rebuild the partition

alter index <index_name> rebuild partition u2018<partition_name>u2019;

like in the case here:

atler index '"SAPSR3".'"/BIC/B0000117000" rebuild partition '"/BIC/B0000117000";

but make sure, this is not the permanat solution of the other warning you are getting in updatestats here.refer the notes

thanks

Bhudev

[Former Member]

Hi,

To avoid the above error we should use the Function Module RSDU_TAB_UNIQUE_INDX_LOCAL, this function module guarantees that the index is created as partitioned.

regards.

[former_member204746]

temporary fix:

sqlplus "/ as sysdba"

alter index "SAPSR3"."/BIC/B0000117000KE" rebuild online;

exit;

good luck.

[Former Member]

Hello

Please try this SAP Note 582427 - Error due to inconsistent Oracle DDIC

Regards

Shishir

[Former Member]

Hello Bhuvaneswari

what is db version, 9i or 10g ?

you have to go through the Notes carefully

Note 408532 - Using the DBMS_STATS package for collecting statistics

Note 633914 - Error when executing DBMS_STATS

Note 351163 - Creating ORACLE DB statistics using DBMS_STATS

Note 424239 - New BRCONNECT parameter: stats_dbms_stats

thanks

Bhudev

[Former Member]

and try to rebuild that unpationed index too