ORA-12012: error on auto execute of job 8913

這個問題我以前遇見過，不過沒有仔細研究，今天多虧棉花幫助，要不然我還是無處下手。

[@more@]

alert：

Mon Aug 11 22:00:37 2008
Errors in file /oracle/app/oracle/admin/1/bdump/1_j000_3285088.trc:
ORA-12012: error on auto execute of job 8913
ORA-03211: The segment does not exist or is not in a valid state

trace：

Dump file /oracle/app/oracle/admin/1/bdump/1_j000_3285088.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters and Data Mining options
ORACLE_HOME = /oracle/app/oracle/product/db10g
System name: AIX
Node name: sczfdb1
Release: 3
Version: 5
Machine: 00C04F0E4C00
Instance name: zfora01
Redo thread mounted by this instance: 1
Oracle process number: 319
Unix process pid: 3285088, image: (J000)

*** ACTION NAME:(AUTO_SPACE_ADVISOR_JOB) 2008-08-11 22:00:37.873
*** MODULE NAME:(DBMS_SCHEDULER) 2008-08-11 22:00:37.873
*** SERVICE NAME:(SYS$USERS) 2008-08-11 22:00:37.873
*** SESSION ID:(1547.44541) 2008-08-11 22:00:37.873
*** 2008-08-11 22:00:37.873
ORA-12012: error on auto execute of job 8913
ORA-03211: The segment does not exist or is not in a valid state

select * from dba_jobs where job=8913

no rows selected.

以上是現象，不過我沒有細緻觀察，其實從上面也能找到可用資訊。

經過棉花指點，我查到

select job_name,failure_count from dba_scheduler_jobs

PURGE_LOG 0
FGR$AUTOPURGE_JOB 0
GATHER_STATS_JOB 0
AUTO_SPACE_ADVISOR_JOB 13

這個Scheduler job AUTO_SPACE_ADVISOR_JOB 失敗了13次，

select * from dba_scheduler_job_log能查到具體資訊，從時間上看與alert中報錯時間相符，說明就是這個Scheduler job 失敗了。如果仔細看，在trace也能發現，不過我們當時也不懂AUTO_SPACE_ADVISOR_JOB 是啥玩意。

首先，Scheduler job 是什麼東東？我個人認為是oracle自己定義的job，比如可以看到GATHER_STATS_JOB ，這是10g後oracle自動收集統計資訊的job

其次，為什麼這個job會失敗了，透過第二條資訊去分析了。

ORA-03211: The segment does not exist or is not in a valid state

Cause: The segment specified in the DBMS_SPACE_ADMIN or DBMS_SPACE operation does not exist or is not in a state appropriate for this operation

Action: Fix the segment specification, or put the segment in the appropriate state.

欲知後事如何，且聽下回分解。