GATHER_STATS_JOB错误及定位

alter日志显示：
[quote]Fri Jun 19 10:00:05 2009
Errors in file /ora10g/app/admin/mctest/bdump/mctest_j001_12715.trc:
ORA-00600: internal error code, arguments: [15470], [0x3DCD09EC], [], [], [], [], [], []
Fri Jun 19 10:00:06 2009
Errors in file /ora10g/app/admin/mctest/bdump/mctest_j001_12715.trc:
ORA-00600: internal error code, arguments: [15470], [0x3DCD09EC], [], [], [], [], [], []
Fri Jun 19 10:00:07 2009
Errors in file /ora10g/app/admin/mctest/bdump/mctest_j001_12715.trc:
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
ORA-06512: at line 5
ORA-06512: at "ASSET.TOPSEXCEPTION", line 30
ORA-06512: at line 1
ORA-06512: at line 56
ORA-00600: internal error code, arguments: [15470], [0x3DCD09EC], [], [], [], [], [], []
Fri Jun 19 10:00:07 2009
Errors in file /ora10g/app/admin/mctest/bdump/mctest_j001_12715.trc:
ORA-00600: internal error code, arguments: [4406], [0x3DCD09EC], [0x3DCCFF94], [], [], [], [], []
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
ORA-06512: at line 5
ORA-06512: at "ASSET.TOPSEXCEPTION", line 30
ORA-06512: at line 1
ORA-06512: at line 56
ORA-00600: internal error code, arguments: [15470], [0x3DCD09EC], [], [], [], [], [], []
Fri Jun 19 10:00:09 2009
Errors in file /ora10g/app/admin/mctest/bdump/mctest_j001_12715.trc:
ORA-00600: internal error code, arguments: [ORA-00600: internal error code, arguments: [4406], [0x3DCD09EC], [0x3DCCFF94], [], [], [], [], []
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
ORA-06512: at line 5
ORA-06512: at "ASSET.TOPSEXCEPTION", line 30
ORA-06512: at line 1
ORA-06512: at line 56
ORA-00600: internal error code, arguments: [15470], [0x3DCD09EC], [], [], [], [], [], []
ORA-06512: at "SYS.DBMS_STATS", line 19089
], [], [], [], [], [], [], [][/quote]

其trc文件显示
[quote][ora10g@mcprod admin]$ more /ora10g/app/admin/mctest/bdump/mctest_j001_12715.trc
/ora10g/app/admin/mctest/bdump/mctest_j001_12715.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /ora10g/oracle/product/10.2.0/db_1
System name: Linux
Node name: mcprod
Release: 2.6.18-53.el5PAE
Version: #1 SMP Mon Nov 12 02:55:09 EST 2007
Machine: i686
Instance name: mctest
Redo thread mounted by this instance: 1
Oracle process number: 31
Unix process pid: 12715, image: oracle@mcprod (J001)

*** ACTION NAME:(GATHER_STATS_JOB) 2009-06-19 10:00:05.410
*** MODULE NAME:(DBMS_SCHEDULER) 2009-06-19 10:00:05.410
*** SERVICE NAME:(SYS$USERS) 2009-06-19 10:00:05.410
*** CLIENT ID:() 2009-06-19 10:00:05.410
*** SESSION ID:(131.2) 2009-06-19 10:00:05.410
*** 2009-06-19 10:00:05.410
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [15470], [0x3DCD09EC], [], [], [], [], [], []
Current SQL statement for this session:
begin :1:=dbms_transaction.local_transaction_id;exception when others then null ;end;
----- PL/SQL Call Stack -----
object line object
handle number name
0x3a4c6600 104 package body SYS.DBMS_TRANSACTION
0x3a4d3c1c 1 anonymous block
[b]0x3a676a38 45 package body ASSET.TASSETAPI
0x3a676a38 634 package body ASSET.TASSETAPI[/b]
0x3a67b494 3 anonymous block
0x3a6ace40 41 anonymous block
0x39fb62bc 692 package body SYS.DBMS_STATS_INTERNAL
0x3a09fcb4 9854 package body SYS.DBMS_STATS
0x3a09fcb4 10560 package body SYS.DBMS_STATS
0x3a09fcb4 13153 package body SYS.DBMS_STATS
0x3a09fcb4 19015 package body SYS.DBMS_STATS
。。。
TABLE: "SYS"."WRI$_OPTSTAT_TAB_HISTORY".""
TABLE: "SYS"."HIST_HEAD$".""
TABLE: "SYS"."WRI$_OPTSTAT_HISTHEAD_HISTORY".""
TABLE: "SYS"."TAB$".""
TABLE: "SYS"."SEG$".""
TABLE: "SYS"."IND$".""
TABLE: "SYS"."TRIGGER$".""
TABLE: "SYS"."OBJ$".""
TABLE: "SYS"."WRH$_SYSMETRIC_SUMMARY".""
TABLE: "SYS"."WRI$_OPTSTAT_HISTGRM_HISTORY".""
TABLE: "SYS"."CDEF$".""
TABLE: "SYS"."LINK$".""
TABLE: "SYS"."JOB$".""
TABLE: "SYS"."EXTERNAL_TAB$".""
TABLE: "SYS"."EXTERNAL_LOCATION$".""
ORA-00600: internal error code, arguments: [ORA-00600: internal error code, arguments: [4406], [0x3DCD09EC], [0x3DCCFF94], [], [], [], [], []
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
ORA-06512: at line 5
ORA-06512: at "ASSET.TOPSEXCEPTION", line 30
ORA-06512: at line 1
ORA-06512: at line 56
ORA-00600: internal error code, arguments: [15470], [0x3DCD09EC], [], [], [], [], [], []
ORA-06512: at "SYS.DBMS_STATS", line 19089
], [], [], [], [], [], [], []
OPIRIP: Uncaught error 447. Error stack:
ORA-00447: fatal error in background process
ORA-00600: internal error code, arguments: [ORA-00600: internal error code, arguments: [4406], [0x3DCD09EC], [0x3DCCFF94], [], [], [], [], []
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
ORA-06512: at line 5
ORA-06512: at "ASSET.TOPSEXCEPTION", line 30
ORA-06512: at line 1
ORA-06512: at line 56
ORA-00600: internal error code, arguments: [15470], [0x3DCD09EC], [], [], [], [], [], []
ORA-06512: at "SYS.DBMS_STATS", line 19089
], [], [], [], [], [], [], [][/quote]

可以看到此job执行一共失败了26次

SQL> select t.job_name,t.owner,t.failure_count from dba_scheduler_jobs t
  2  where t.job_name='GATHER_STATS_JOB'
  3  ;

JOB_NAME                       OWNER                          FAILURE_COUNT
------------------------------ ------------------------------ -------------
GATHER_STATS_JOB               SYS                                       26

手工执行job，报错依然，经检查是业务程序引起的，注意黑体部分，修正后问题解决，再次执行成功

SQL>  exec  dbms_stats.gather_database_stats_job_proc

PL/SQL procedure successfully completed.