Dec 30, 2013

ORA-00600: internal error code, arguments: [15851]

-- ORA-00600: internal error code, arguments: [15851], [3], [2], [1], [1], [], [], [], [], [], [], []
-- Issue faced in Oracle 11gR2

sql> EXEC dbms_stats.gather_schema_stats('HR', cascade=>TRUE);

ORA-00600: internal error code, arguments: [15851], [3], [2], [1], [1], [], [], [], [], [], [], []

*Cause: 

> This is the generic internal error number for Oracle program exceptions.
> This indicates that a process has encountered an exceptional condition.
> Due to some tables stats failure, the entire schema gather failed.

*Action: 

> Report as a bug - the first argument is the internal error number.
> Create dynamic script to gather all tables for the schema and find what tables stats not gathered and failed with 'ORA-00600'.                                                                                                   

-- Dynamic script: ( Asume schema name/ owner of tables is 'HR'):
SQL > select ' exec dbms_stats.gather_table_stats('''||d.owner||''','''||d.table_name||''','
||'cascade => true'||');' from all_tables d where owner='HR' order by table_name;

-- Sample example to show which table failed with error.

SQL> exec dbms_stats.gather_table_stats('HR','EMPLOYEE');

BEGIN dbms_stats.gather_table_stats('HR','EMPLOYEE'); END;
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [15851], [3], [2], [1], [1], [], [],[], [], [], [], []
ORA-06512: at "SYS.DBMS_STATS", line 20337
ORA-06512: at "SYS.DBMS_STATS", line 20360
ORA-06512: at line 1

*Solution:
Try-1:

SQL> EXEC dbms_stats.gather_TABLE_stats(OWNNAME=>'HR',TABNAME=>'EMPLOYEE');

-- if above/ same error came then,
Try-2:
SQLl> exec DBMS_STATS.gather_table_stats(ownname=> 'HR',tabname=>'EMPLOYEE', estimate_percent => 5,cascade=>TRUE, degree=>1,granularity=>'AUTO',method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO');

-- if above/ same error came then,
Try-3:

SQL> exec DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
SQL> exec DBMS_STATS.gather_table_stats(ownname=> 'HR',tabname=>'EMPLOYEE',
estimate_percent => 50,cascade=>TRUE,degree=>1,granularity=>'AUTO',method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO');

Note: Increase estimate % with some larger value. It will work

-- if above/ same error came then,
Try-4:
SQL> exec DBMS_STATS.gather_table_stats(ownname=> 'HR',tabname=>'EMPLOYEE', estimate_percent => 10, cascade=>TRUE,degree=>1,granularity=>'AUTO',method_opt=>'FOR ALL INDEXED COLUMNS SIZE 1');

Click here to view solution for Other ORA-00600

Sure it will work.
Don't forget to post comments.

No comments:

Post a Comment

Translate >>