Update statistics getting failed

[Saikumar]

Hi All,

I am getting an error while updating the statistics  as below.

BR0301E SQL error -20003 in thread 4 at location stats_tab_collect-69, SQL statement:

'EXECUTE stmt_1d'

'BEGIN DBMS_STATS.GATHER_TABLE_STATS (OWNNAME => '"SAPSR3"', TABNAME => '"ACCTIT"', ESTIMATE_PERCENT => 10, METHOD_OPT => 'FOR ALL COLUMNS SIZE 1', DEGREE => NULL, CASC

ADE => TRUE, NO_INVALIDATE => FALSE); END;'

ORA-20003: Specified bug number (9196440) does not exist

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

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

ORA-06512: at line 1

BR0886E Collecting statistics failed for table SAPSR3.ACCTIT

I have gone through Note and tried to relink all. But still problem persists.

1667086 - ORA-20003 - Specified bug number (<number>) does not exist.

I also found no Mopatch/Opatch directories  under oracle home. I have restored them from one of my backup.

Do I need to run again below scripts .

@?/rdbms/admin/dbmsstat.sql

  @?/rdbms/admin/prvtstas.plb

  @?/rdbms/admin/prvtstat.plb

[Former Member · Accepted solution]

Hi,

Try to run this command and see if it fixes it .

sql>GRANT CREATE JOB TO SAPDBA;

Another thing what is the Oracle version as i can see the bug fix in the below note for 11.2.0

1431798 - Oracle 11.2.0: Database Parameter Settings

Note 1667086 - ORA-20003 - Specified bug number (<number>) does not exist.

Looks to be similar hope you are checking the correct oracle home.

'9196440:ON'

UNIX SBP 11201x_date ( date >= 201009 ) UNIX SBP 11202x_date ( date >= 201011 ) UNIX SGR 11202x_date ( date >= 201106 ) UNIX SXD 11202x_date ( date >= 201106 ) UNIX 11.2.0.3 WIN 11.2.0.1.nP ( n >= 4 ) WIN 11.2.0.2 WIN 11.2.0.3

Thanks

Rishi Abrol

[Saikumar · Accepted solution]

HI Rishi & Ashish ,

Forget to mentioned our oracle version is as below.

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi

PL/SQL Release 10.2.0.5.0 - Production

CORE    10.2.0.5.0      Production

TNS for HPUX: Version 10.2.0.5.0 - Production

NLSRTL Version 10.2.0.5.0 - Production

And also applied the solution give in metalink ID 1180514.1

Thank you.

Regard's

Sai

[Former Member · Accepted solution]

Hi,

Have you applied this Merge fix as per the below note.

1525673 - Optimizer merge fix for Oracle 10.2.0.5

And

'9196440:ON' (10.2.0.4 with fix 9196440 from Note 1165319

SBP 10205X_DATE  (DATE >=201011))

UNIX SBP 10204x_date   (date  >=  201007)

UNIX SBP 10205x_date   (date  >=  201011)

WIN 10.2.0.4.nP   (n >= 38)

WIN  10.2.0.5.nP  (n  >=  2)

830576 - Parameter recommendations for Oracle 10g

Did you try to run the command.

Thanks

Rishi Abrol

[Former Member · Accepted solution]

Hi,

Have you applied this Merge fix as per the below note.

1525673 - Optimizer merge fix for Oracle 10.2.0.5

And

'9196440:ON' (10.2.0.4 with fix 9196440 from Note 1165319

SBP 10205X_DATE  (DATE >=201011))

UNIX SBP 10204x_date   (date  >=  201007)

UNIX SBP 10205x_date   (date  >=  201011)

WIN 10.2.0.4.nP   (n >= 38)

WIN  10.2.0.5.nP  (n  >=  2)

830576 - Parameter recommendations for Oracle 10g

Did you try to run the command.

Thanks

Rishi Abrol

[ashish_vikas · Accepted solution]

Verify _FIX_CONTROL for 9196440 as per note 830576  - Parameter recommendations for Oracle 10g

best regards

ashish

[Saikumar · Accepted solution]

HI Ashish & Rishi ,

I run the below scripts in startup restrict mode. And started update again  as of now it has reached 6% initial it was failing at 1 % with error message. Let me observe for some more time. 🙂 . And fix_control is as below.

NAME                                 TYPE        VALUE

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

_fix_control                         string      4728348:off

@?/rdbms/admin/dbmsstat.sql

  @?/rdbms/admin/prvtstas.plb

  @?/rdbms/admin/prvtstat.plb

Regard's

Sai

[Saikumar · Accepted solution]

Dear All,

My issue is resolved  guess update statistics is going on smooth now.

Just I have run the below scripts in startup restrict mode. This worked out for me. Thank you Rishi& Ashish for your quick response and suggestions.

@?/rdbms/admin/dbmsstat.sql

  @?/rdbms/admin/prvtstas.plb

  @?/rdbms/admin/prvtstat.plb

Thank you.

Regard's

Sai

[ashish_vikas]

Hello Saikumar Mudragada,

What is your Oracle db version and SBP level ? you can apply latest SBP patch for your db.

you should also check your oracle parameters, specially FIX_CONTROL parameters.

1431798  - Oracle 11.2.0: Database Parameter Settings

1171650  - Automated Oracle DB parameter check

best regards

ashish