1. 手动运行Job
括号中为job号
1.1 使用sys用户运行
exec dbms_ijob.run(4)
1.2 使用本身用户运行
exec dbms_job.run(4)
2. 删除Job
exec dbms_ijob.remove(862)
commit;
3. 停止&开启job
EXEC DBMS_IJOB.BROKEN(1591,TRUE);
COMMIT;
EXEC DBMS_IJOB.BROKEN(552,FALSE);
COMMIT;
4. 设置Job可同时运行数量
设置为0表示禁止job运行
ALTER SYSTEM SET job_queue_processes =20;
5. 建立Job
5.1 每五分钟运行一次
declare test_job_really number ;
begin
dbms_job.submit(test_job_really,'dcprod_queue_job;',sysdate ,'sysdate+5/1440');
commit;
end;
5.2 每周日20点执行(立即执行一次)
declare test_job_really number ;
begin
dbms_job.submit(job=>test_job_really,
what=>'PKG_BONUS_JOB.EXECUTE_WEEKLY;',
next_date=>sysdate ,
Interval => 'NEXT_DAY(TRUNC(SYSDATE),1)+20/24'
);
commit;
end;
5.3 每周日4点执行(不立即执行)
declare job number;
begin
sys.dbms_job.submit(job,'PKG_NME_BONUS.AutoRunData;',
to_date('25-06-2017 04:00:00', 'dd-mm-yyyyhh24:mi:ss'),
'TRUNC(next_day(sysdate,1))+4/24');
commit;
end;
5.4 每天七点运行
declare test_job_really number ;
begin
dbms_job.submit(job=>test_job_really,
what=>'dcb2b.P_SAPAccount_SendMail;',
next_date=>to_date('18-04-201707:00:00','dd-mm-yyyy hh24:mi:ss'),
Interval => 'TRUNC(sysdate) + 1 +7/24'
);
commit;
end;
5.5 每三个月的的一号(从当前月份算起)
declare test_job_really number ;
begin
dbms_job.submit(job=>test_job_really,
what=>'P_EXT_SendMail;',
next_date=>sysdate,
Interval => 'ADD_MONTHS(TRUNC(SYSDATE,''MM''),3)'
);
commit;
end;
6. 更改Job执行频率
更改至下一个小时
BEGIN
DBMS_JOB.CHANGE(289,null,null,'TRUNC(SYSDATE,''HH24'')+1/24');
END;
