Oracle 8i/9i/10g dba SCHEDULED JOBS 排程失敗 , 重新enable的方法

如果排程定義中, 相關的function 或prodedure 為無效的(INVALID)，當然會失敗~
另外也有可能view , synonym 或 db link無效都有可能造成~

查詢Failure jobs :


col job_owner format a15
col job_definition format a30 word_wrap
SELECT  job        
       ,log_user     job_owner
       ,failures
       ,broken
       ,what         job_definition
  FROM
        dba_jobs
 WHERE
        broken = 'Y' OR NVL(failures,0) > 0 ;


Re-enable Jobs:

select 'exec dbma_job.broken('||job||','||'FALSE);' from dba_jobs WHERE  (broken = 'Y' and failures is not null) OR NVL(failures,0) > 0 ;

exec dbma_job.broken(10,FALSE);
exec dbma_job.broken(11,FALSE);
exec dbma_job.broken(13,FALSE);

Updated 2013/04/02

如果running job 無法停止, 可以使用以下指令kill , kill完之後, job會自動再帶起並且執行.


begin
 dbms_job.broken(15609,true);
end;

Marking the job as Broken is necessary; otherwise, the job queue process will restart the job as soon as it notices the job has been killed.