Oracle 定时任务 dbms_job

一、主要操作

1. 创建job

variable jobno number;
dbms_job.submit(:jobno, —-job号，系统自动分配
 'your_procedure;',—-执行的存储过程, ';'不能省略 
 next_date, —-下次执行时间 
 'interval' —-每次间隔时间，interval以天为单位
);
/
COMMIT;

-- 例如
variable job number;
begin
  sys.dbms_job.submit(job => :job,
                      what => 'gtadmin.ilm_manage_pkg.ilm_mv_test(180,365,''HOT'',''WARM'',''USER'',''TABLE'');',
                      next_date => to_date('2019-12-25 20:00:00', 'YYYY-MM-DD HH24:MI:SS'),
                      interval => 'SYSDATE + 6/24');
end;
/
COMMIT;

2. 删除job

普通用户删除自己创建的job

declare
begin
  dbms_job.remove(jobno);
  commit;
end;
/

sys用户删除其他用户job

declare
begin
  dbms_ijob.remove(3);
  commit;
end;
/

3. 修改要执行的内容

exec dbms_job.what(jobno, what);  
commit;

4. 修改下次执行时间

exec dbms_job.next_date(jobno, next_date);
commit;

5. 修改间隔时间

exec dbms_job.interval(jobno, interval); 
commit;

6. 启动job

exec dbms_job.run(jobno);
commit;

7. 停止（broken）job

exec dbms.broken(jobno, broken, nextdate); –broken为boolean值
commit;

--例如
begin
 dbms_job.broken(25, true, sysdate);
 commit;
end;
/

查看broken的job信息

select JOB,SCHEMA_USER,NEXT_DATE,NEXT_SEC,BROKEN,FAILURES,WHAT from dba_jobs where BROKEN='Y';

恢复broken job

exec dbms_ijob.broken(141,false);
commit;

二、初始化参数 job_queue_processes

job_queue_process表示oracle能并发的job数，当值为0时表示全部停止job。

查看job_queue_processes参数

show parameter job_queue_process;

修改job_queue_processes参数

alter system set job_queue_processes = 10;

三、dba_jobs表结构

 字段（列）             数据类型             字段描述 
 JOB                    NUMBER               任务的唯一标示号 
 LOG_USER               VARCHAR2(30)         提交任务的用户 
 PRIV_USER              VARCHAR2(30)         赋予任务权限的用户 
 SCHEMA_USER            VARCHAR2(30)         对任务作语法分析的用户模式 
 LAST_DATE              DATE                 最后一次成功运行任务的时间 
 LAST_SEC               VARCHAR2(8)          如HH24:MM:SS格式的last_date日期的小时，分钟和秒 
 THIS_DATE              DATE                 正在运行任务的开始时间，如果没有运行任务则为null 
 THIS_SEC               VARCHAR2(8)          如HH24:MM:SS格式的this_date日期的小时，分钟和秒 
 NEXT_DATE              DATE                 下一次定时运行任务的时间 
 NEXT_SEC               VARCHAR2(8)          如HH24:MM:SS格式的next_date日期的小时，分钟和秒 
 TOTAL_TIME             NUMBER               该任务运行所需要的总时间，单位为秒 
 BROKEN                 VARCHAR2(1)          标志参数，Y标示任务中断，以后不会运行 
 INTERVAL               VARCHAR2(200)        用于计算下一运行时间的表达式 
 FAILURES               NUMBER               任务运行连续没有成功的次数 
 WHAT                   VARCHAR2(2000)       执行任务的PL/SQL块 
 CURRENT_SESSION_LABEL  RAW MLSLABEL         该任务的信任Oracle会话符 
 CLEARANCE_HI           RAW MLSLABEL         该任务可信任的Oracle最大间隙 
 CLEARANCE_LO           RAW MLSLABEL         该任务可信任的Oracle最小间隙 
 NLS_ENV                VARCHAR2(2000)       任务运行的NLS会话设置 
 MISC_ENV               RAW(32)              任务运行的其他一些会话参数

四、 关于job运行时间

1. 常规设置

每分钟执行

Interval => TRUNC(sysdate,'mi') + 1/(24*60)

每个小时的第15分钟运行，比如 8:15，9:15，10:15…

Interval => trunc(sysdate,'hh')+(60+15)/(24*60)

每半个小时执行

Interval => sysdate+30/(24*60)

每天定时执行，例如每天6点执行

Interval => trunc(sysdate+1)+6/24 

每周定时执行，例如每周一凌晨1点执行

Interval => TRUNC(next_day(sysdate,''星期一''))+1/24
--或者
interval => TRUNC(next_day(sysdate,''MON''))+1/24

每月定时执行，例如每月1日凌晨1点执行

Interval =>TRUNC(LAST_DAY(SYSDATE))+1+1/24

每季度（每三个月）定时执行，例如每季度的第一天凌晨1点执行

Interval => TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 1/24

每半年定时执行，例如每年1月1日和7月1日凌晨1点

Interval => ADD_MONTHS(trunc(sysdate,'yyyy'),6)+1/24

每年定时执行，例如每年1月1日凌晨1点执行

Interval =>ADD_MONTHS(trunc(sysdate,'yyyy'), 12)+1/24

另外要特别注意边界时间计算

https://oracleblog.org/working-case/how-to-calculate-job-next_date/

2. 根据不同条件使用不同执行间隔

每天8-20点每隔15分钟运行一次，其余时间不运行。注意如果部署该job时不在8到20点范围内，可能需要指定next_date参数，否则会在第二天8点才开始运行。

interval=> case when to_number(to_char(sysdate,'hh24'))>=8 and to_number(to_char(sysdate,'hh24'))<=20 then sysdate+15/1440 else trunc(sysdate+1)+8/24 end

每个月的1-4号，每隔半小时运行一次

interval=> case when to_number(to_char(sysdate,'dd'))>=1 and to_number(to_char(sysdate,'dd'))<=4 then sysdate+30/1440 else trunc(last_day(sysdate))+1 end

6-10月，每隔30天在0点运行，其余月份每隔一天早上4点运行

interval => case when to_number(to_char(sysdate,'mm'))>=6 and to_number(to_char(sysdate,'mm'))<=10 then trunc(sysdate+30) else trunc(sysdate+1)+4/24 end

参考

https://blog.csdn.net/wang_chaoju/article/details/4502957