[Oracle数据库日常操作]Oracle Job常用操作

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-yyyy hh24: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-2017 07: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;