Update Stats Jobs Failed Frequently in Database Planning Calendar

[Former Member]

Hello Gurus

In our BW production server update stats jobs failed frequently in a week

I am attaching log of Update Stats

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

'BEGIN DBMS_STATS.GATHER_TABLE_STATS (OWNNAME => '"SAPR3"', TABNAME => '"/BIC/FZPS_C04"', ESTIMATE_PERCENT => 1, METHOD_OPT => 'FOR COLUMNS SIZE 150

ORA-20000: partition "/BIC/FZPS_C040000010858" of index "SAPR3"."/BIC/FZPS_C04~090" does not exist or insufficient privileges

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 SAPR3./BIC/FZPS_C04

What I am doing : brconnect -u system/******** -c -f stats -t missing

Please guide me whether I am doing the right process, why it is giving me error

Thanks & Regards

Shishir

[fidel_vales · Accepted solution]

Hi,

check if any of the following notes is valid for you:

• 633914 Error when executing DBMS_STATS (point 6)

• 963760 'ORA-20000: Insufficient privileges' for creating statistics

• 948197 Merge fix for DBMS_STATS package on Oracle 9.2.x and 10.2.x

I'd assume it is the last one the one that you are looking for

[Former Member]

Thanks

[former_member204746]

Other possibility, you are trying to update stats while cubes are being loaded/updated.

try another time to recalc DB stats or ask your DB functionals to move their loads.

[Former Member]

This should solve your problem

Log on to Oracle SQL+ as SYSDBA and assign the following rights to the SAPCONN role if you are already using Oracle 10.2 or higher.

SQL> connect / as sysdba

SQL> grant analyze any to sapconn;

If you are still using Oracle 10.1, assign

the rights to the SAP<sid> or SAPR3 user:

SQL> connect / as sysdba

SQL> grant analyze any to SAP<sid>;