Oracle ORA-23421: job number N is not a job …

今天要將測試區的job全部停用時,以下列的語法來執行:

declare
    cursor cur1 is 
        select job from dba_jobs where broken='N' and schema_user <>'TOAD' and job <> 1 ;
    r1 dba_jobs%rowtype;
    job_check number;
    job_broken varchar2(5);
begin
    for r1 in cur1 loop
        dbms_job.broken(r1.job,true);
        select job,broken into job_check,job_broken
            from dba_jobs where job=r1.job;
         dbms_output.put_line( 'JOB: ' || job_check ||  '    Broken: ' || job_broken);
    end loop;
end; 

結果出現了ORA的錯誤:

ORA-23421: job number 48 is not a job in the job queue

似乎是因為job的Owner不是目前登入User,所以無法停用,不過我是以sys的身份登入,竟然也是無法成功,最後在google上找到一個可以解決的方式,就是改用dbms_ijob.broken。

dbms_job.broken(r1.job,true); 改為 dbms_ijob.broken(r1.job,true);