oracle dbms ijob,Oracle Job常用操作

Oracle Job常用操作

原创 2017-10-10 Oracle 宅必备

这个专题主要是一些日常运维中需要用到的命令，不定期更新~~

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;