oracle job重建

乐刚网-job重建

--检查所有的job
set lines 200 pages 200
col last_date for a19
col this_date for a19
col next_date for a19
col interval for a40
col what for a40
select job,broken ,
       to_char(last_date,'yyyy-mm-dd hh24:mi:ss') as last_date,
       to_char(this_date,'yyyy-mm-dd hh24:mi:ss') as this_date,
       to_char(next_date,'yyyy-mm-dd hh24:mi:ss') as next_date,
       interval,failures,what from dba_jobs 
where schema_user='SCOTT'
  and job=346
order by last_date;

Please Input WebServer  Type : 6
       JOB BRO LAST_DATE           THIS_DATE           NEXT_DATE           INTERVAL                                   FAILURES WHAT
---------- --- ------------------- ------------------- ------------------- ---------------------------------------- ---------- ----------------------------------------
       346 N   2022-06-30 00:30:02                     2022-07-01 17:33:27 TRUNC(sysdate+1)+(0*60+30/1440)                   9 SCOTT.sp_proc_prod;

重建该job
--检查所有的job
set lines 200 pages 200
col last_date for a19
col this_date for a19
col next_date for a19
col interval for a40
col what for a40
select job,broken ,
       to_char(last_date,'yyyy-mm-dd hh24:mi:ss') as last_date,
       to_char(this_date,'yyyy-mm-dd hh24:mi:ss') as this_date,
       to_char(next_date,'yyyy-mm-dd hh24:mi:ss') as next_date,
       interval,failures,what from dba_jobs 
where schema_user='SCOTT'
  and job=346
order by last_date;

       JOB BRO LAST_DATE           THIS_DATE           NEXT_DATE           INTERVAL                                   FAILURES WHAT
---------- --- ------------------- ------------------- ------------------- ---------------------------------------- ---------- ----------------------------------------
       346 N   2022-06-30 00:30:02                     2022-07-01 17:33:27 TRUNC(sysdate+1)+(0*60+30/1440)                   9 SCOTT.sp_proc_prod;

重建job
1、关闭该的job
--禁用job
exec dbms_job.broken(346,true);
commit;

2、删除该的job
exec dbms_job.remove(346);
commit;

3、重建该job
var job_num number;
begin
    dbms_job.submit(JOB  => :job_num,
                    WHAT => 'SCOTT.sp_proc_prod;',
                    NEXT_DATE => to_date('2022-07-02 00:30:00','yyyy-mm-dd hh24:mi:ss'),
                    INTERVAL =>'TRUNC(sysdate+1)+(0*60+30/1440)'
                    );
    commit ;
end;
/

--检查所有的job
set lines 200 pages 200
col last_date for a19
col this_date for a19
col next_date for a19
col interval for a40
col what for a40
select job,broken ,
       to_char(last_date,'yyyy-mm-dd hh24:mi:ss') as last_date,
       to_char(this_date,'yyyy-mm-dd hh24:mi:ss') as this_date,
       to_char(next_date,'yyyy-mm-dd hh24:mi:ss') as next_date,
       interval,failures,what from dba_jobs 
where schema_user='SCOTT'
  --and job=223
  and what='SCOTT.sp_proc_prod;'
order by last_date;

       JOB BRO LAST_DATE           THIS_DATE           NEXT_DATE           INTERVAL                                   FAILURES WHAT
---------- --- ------------------- ------------------- ------------------- ---------------------------------------- ---------- ----------------------------------------
       366 N                                           2022-07-02 00:30:00 TRUNC(sysdate+1)+(0*60+30/1440)                     SCOTT.sp_proc_prod;