cancel
Showing results for 
Search instead for 
Did you mean: 

Check and update optimizer statistics job finished with errors in BW system

0 Kudos

Hi,

Check and update optimizer statistics job terminated with errors in BW system.

Might be the index was dropped for loading date at the time when the statistic update ran, i have checked the timings error occurred and creation time of INDEX as below.


BR0280I BRCONNECT time stamp: 2014-07-11 04.50.52

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

'BEGIN DBMS_STATS.GATHER_INDEX_STATS (OWNNAME => '"SAPSR3"', INDNAME => '"/BIC/FZSDC0010~020"', ESTIMATE_PERCENT => 3, DEGREE => NULL, GRANULARITY => 'ALL', NO_INVALIDATE => FALSE); END;'

ORA-20000: Unable to set values for index /BIC/FZSDC0010~020: does not exist or insufficient privileges

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

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

ORA-06512: at line 1

BR0886E Collecting statistics failed for index SAPSR3./BIC/FZSDC0010~020

BR0280I BRCONNECT time stamp: 2014-07-11 04.50.52

SQL> select object_name, object_type, last_ddl_time, created,TIMESTAMP,STATUS from dba_objects where object_type = 'INDEX' and object_name='/BIC/FZSDC0010~020';

OBJECT_NAME                                                                                                                      OBJECT_TYPE

-------------------------------------------------------------------------------------------------------------------------------- -------------------

LAST_DDL_TIME   CREATED         TIMESTAMP           STATUS

--------------- --------------- ------------------- -------

/BIC/FZSDC0010~020                                                                                                               INDEX

25-JUL-14       25-JUL-14       2014-07-25:04:56:26 VALID

But the Check and update optimizer statistics job completes successful once in a week and remaining times it collects statistics for 90% approx at this time Collecting statistics for indexes fails and ends up with errors.


Please suggest how to resolve the issue.



Thanks&Regards

Chandrasehkar

Accepted Solutions (0)

Answers (1)

Answers (1)

ashish_vikas
Active Contributor
0 Kudos
0 Kudos

Hi Ashish,

Thanks for the reply.

The issue is not regarding with insufficient privileges ,it is with "INDEX  does not exist" at the time of update statistics run and the index is recreated later during data loads.


Regards

Chandrashekar


former_member188883
Active Contributor
0 Kudos

Hi Chandrashekar,

What is the kernel and dba tools patch level ?

Are the brbackups running fine ?

Regards,

Deepak Kori

0 Kudos

Hi,

Kernel Release 720 and dba tools patch level 8.

brbackups are running fine and also update statistics job runs successful once or twice in a week.

Regards

Chandrashekar

yrk_tkm
Explorer
0 Kudos

Hi,

Did you manage to solve the problem? How?