今天要將測試區的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);