Problem in UpdateStats(update database statistics)

[Former Member]

Hello Experts,

I am getting this problem while running job UpdateStats, job failed with this error in Job Log:

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

'BEGIN DBMS_STATS.GATHER_INDEX_STATS (OWNNAME => '"SAPSR10"', INDNAME => '"/BIC/B0001751000KE"', ESTIMATE_PERCENT => NULL, DEGREE => NULL, NO_INVALIDATE => FALSE); END;'

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

Current patch Level is 18.

Please suggest solution.

[former_member204746]

other quick fix:

go into SE14

type /BIC/B0001751000 (without the "KE")

clck on EDIT button

click on INDEXES button

choose the KEY index.

IMPORTANT: make sure that you are seeing the index on the screen, and not the table before continuing!

Click on DELETE DATABASE INDEX

click on CREATE DATABASE INDEX.

good luck.

[Former Member]

Thanks Eric

[Former Member]

Hi Amit,

Proceed as follows:

1.Execute the 'RSAR_PSA_PARTITION_CHECK' program without setting the repair indicator.

2.If there are inconsistent secondary indexes in your system, these along with the errors that are described in more detail in Note 1012607 are listed.

3.Now execute the 'RSAR_PSA_PARTITION_CHECK' program in repair mode ( REPAIR = 'X' ) if you are prompted to do so in the second row of the log. We recommend that you execute the 'RSAR_PSA_PARTITION_CHECK' program in the background.

4.Program 'RSAR_PSA_PARTITION_CHECK' can repair incorrect indexes only. If there are other errors in the log, you can correct them manually as described in Note 1012607.

5.You can use transaction SLG1 and the following selections to display the log:

Object = 'RSAR'

Subobject = 'METADATA'

Ext. Identif. = 'RSAR_PSA_PARTITION_CHECK'

You do not need to carry out any more manual activities (for 'ORACLE').

Please check the notes:1042182,633914.

And also please check the below link:

http://wallsdb.vicp.net/bbs/viewthread.php?tid=102

Hope it will resolve your issue.

Thanks

Kishore

[Former Member]

Thanks Kishore