Check and Update Optimizer Statistics Error

[Former Member]

Hello,

I have scheduled 'Check and Update Optimizer Statistics' from DB 13 but I always get this error.

BR0280I BRCONNECT time stamp: 2009-01-05 01.00.46

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

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

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

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

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

ORA-06512: at line 1

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

Tried checking the table /BIC/B0000412000KE and got this from the check:

Enhancement category for table missing

Enhancement category for include or subtype missing

Table /BIC/B0000412000 was checked with warnings

I am still checking what this means but appreciate you have any idea on how to solve it.

Thank you.

Best Regards,

Julius

Edited by: Julius Baron Manuel on Jan 7, 2009 7:39 AM

[Former Member]

Hai,

Check the below link.....

Regards,

Yoganand.V

[Former Member]

Thank you all for your replies.

I tried recreating the index from SE14 but still get the same error as before.

What I did was to run program RSAR_PSA_PARTITION_CHECK and tick the repair flag.

I am now able to update the statistics successfully.

Best Regards,

Julius

[Former Member]

Hi Julius,

Have your tried scheduling the update stats via DB13 'Immediately'. ??? Does it still gives you the same error when its done 'immediately'?

Try out DB02 and check your whole Database along with missing indexes. Refresh database and update histories.

Regards,

Pranay

[Former Member]

Hello Pranay,

Still the same case when scheduled immediately.

I have updated DB02 stats already and from there, there are no missing indexes.

Best Regards,

Julius

[Former Member]

May be you'll have to rebuild your index if its in unstable state.

Refer:

Hope this helps.

Regards,

Pranay.

[Former Member]

also try SE14