Oracle JOB定时任务用法

Oracle JOB定时任务用法

• 创建一个JOB

declare
  job number;
BEGIN
  DBMS_JOB.SUBMIT(  
        JOB => job,  --自动生成JOBID  
        WHAT => 'p_clean_pre;',  --需要执行的存储过程名称或SQL语句，注意;号不能少
        NEXT_DATE =>  TRUNC(sysdate + 2) + 23 / (24),  --初次执行时间，我这里的为2天后的23点
        INTERVAL => 'TRUNC(next_day(sysdate,7))+23/24' --每次执行间隔时间，我这里的为每周六的晚上23点
      );  
  commit;
end;

• 创建好之后可以通过以下SQL来查看创建的情况，有可能这个SQL或存储过程有创建多个JOB，所以最终操作这个JOB还需要通过JOBID去操作

SELECT * FROM USER_JOBS WHERE WHAT = 'p_clean_pre;'

• USER_JOB的字段说明
  
• 手动测试JOB

begin
   DBMS_JOB.RUN(JOBID); 
 end;

• 删除一个JOB

begin
  --删除自动执行的job
  dbms_job.remove(JOBID);
end;

• 停止JOB

--停止一个JOB,broken里面参数true也可是false，next_date（某个时间）也可是sysdate（立刻停止）
begin
	DBMS_JOB.broken(JOBID, broken, nextdate);   
end;

• 修改JOB

begin
	--修改间隔时间
	dbms_job.interval(JOBID,interval);
	--修改下次执行时间
	dbms_job.next_date(JOBID,next_date);
	--修改JOB名称
	dbms_job.what（JOBID,'xxx'）；
end;

• 附：INTERVAL参数常用值示例

：按秒，每一秒执行一次JOB

SYSDATE + 1/(24*60*60) 

：按分，每分钟执行一次JOB

TRUNC(SYSDATE,'mi') + 1/ (24*60)
SYSDATE + 1/（24*60）

：按时，每小时执行一次

SYSDATE + 1/24
TRUNC(SYSDATE,'hh')

：按天

TRUNC(SYSDATE + 1)	--每天0点
TRUNC(SYSDATE) + 23 / 24	--每天23点
TRUNC(SYSDATE) + (8*60+30)/(24*60)	--每天上午8点半

：按周

TRUNC(NEXT_DAY(SYSDATE,'星期六')) + 23/24	--每周六23点
TRUNC(LEAST(NEXT_DAY(SYSDATE, 7), NEXT_DAY(SYSDATE, 1))) + (8*60+30)/(24*60)	--每星期六和日早上8点半

：按月

TRUNC(LAST_DAY(SYSDATE ) + 1)	--每个月第一天0点
TRUNC(LAST_DAY(SYSDATE)) + 2 + 23/24	--每个月2号23点

：按季

TRUNC(ADD_MONTHS(SYSDATE + 2/24, 3 ), 'Q') - 1/24	--每个季度最后一天的23点
TRUNC(ADD_MONTHS(SYSDATE, 3),'q')	--每个季度第一天0点
TRUNC(ADD_MONTHS(SYSDATE,3),'q') + 23/24	--每季度的第一天23执行 

：按年

ADD_MONTHS(TRUNC(SYSDATE,'yyyy'), 6) + 23/24	--每年7月1日和1月1日23点  
ADD_MONTHS(trunc(SYSDATE,'yyyy'),12) + 2 + 23/24	--每年1月3日23执行  

PS: 如果出现JOB任务启动了而不会执行，可能的原因是job_queue_processes 的value太小了，假设该值为3，那么执行的JOB数只能是3个 超过3的就没办法执行了。如果想要更多的JOB运转， 搞大她即可。
查看修改job_queue_processes的SQL:

SELECT VALUE FROM V$PARAMETER WHERE NAME LIKE '%job_queue_processes%' 

ALTER SYSTEM SET job_queue_processes =100;