在oracle 10g之前，我们常使用DBMS_JOB提供的相关软件包来管理oracle数据库中的定时任务，虽然10g之后，更多推荐功能更强大的DBMS_SCHEDULER，但由于习惯问题，很多时候还是会用到DBMS_JOB，下面简单介绍下两种工作的用法。
DBMS_JOB
oracle已job queue队列的形式管理定时任务，因此需要先设置JOB_QUEUE_PROCESSES参数为非0的数值，一般10即可
ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 10 scope=both;
2, DBMS_JOB用法及例子
SUBMIT：提交任务到队列
VARIABLE jobno NUMBER
BEGIN
DBMS_JOB.SUBMIT(:jobno,
‘DBMS_DDL.ANALYZE_OBJECT(‘‘TABLE‘‘,‘‘SYS‘‘, ‘‘TEST‘‘,‘‘ESTIMATE‘‘, NULL, 50);‘,
SYSDATE, ‘SYSDATE + 1‘);
COMMIT;
END;
/
sys@MS4ADB3(dtydb5)> PRINT jobno
JOBNO
----------
1
查看任务执行情况
sys@MS4ADB3(dtydb5)> select job,what,NEXT_DATE from dba_jobs;
JOB WHAT NEXT_DATE
---------- ---------------------------------------------------------------------------------------------------- -------------------
1 DBMS_DDL.ANALYZE_OBJECT(‘TABLE‘,‘SYS‘, ‘TEST‘,‘ESTIMATE‘, NULL, 50); 2014-04-01 14:16:53
有效job的描述例子如下
‘myproc(‘‘10-JAN-99‘‘, next_date, broken);‘ ‘scott.emppackage.give_raise(‘‘JFEE‘‘, 3000.00);‘ ‘dbms_job.remove(job);‘
常用的job时间间隔
‘SYSDATE + 7‘ 每隔7天 ‘SYSDATE + 1/48‘ 每半个小时 ‘NEXT_DAY(TRUNC(SYSDATE), ‘‘MONDAY‘‘) + 15/24‘ 每周一下午三点 ‘NEXT_DAY(ADD_MONTHS(TRUNC(SYSDATE, ‘‘Q‘‘), 3), ‘‘THURSDAY‘‘)‘ 每季度的第一个周四
REMOVE
从队列中删除
BEGIN DBMS_JOB.REMOVE(14144); END; /CHANGE
修改任务的属性
BEGIN DBMS_JOB.CHANGE(14144, NULL, NULL, ‘SYSDATE + 3‘); END; /WHAT
修改任务的描述
BEGIN
DBMS_JOB.WHAT(1,
‘DBMS_DDL.ANALYZE_OBJECT(‘‘TABLE‘‘,‘‘SYS‘‘, ‘‘T2‘‘,‘‘ESTIMATE‘‘, NULL, 50);‘);
END;
/
NEXT_DATE任务下一次执行的时间
BEGIN DBMS_JOB.NEXT_DATE(14144, SYSDATE + 4); END; /INTERVAL
任务执行的时间间隔
BROKEN
终止任务的执行
RUN
运行任务
BEGIN DBMS_JOB.RUN(1); END; /
查看job的相关信息，可以查看视图DBA_JOBS和DBA_JOBS_RUNNING
sys@MS4ADB3(dtydb5)> SELECT JOB, NEXT_DATE, NEXT_SEC, FAILURES, BROKEN
2 FROM DBA_JOBS;
JOB NEXT_DATE NEXT_SEC FAILURES BR
---------- ------------------- ---------------------------------------------------------------- ---------- --
1 2014-04-01 14:39:39 14:39:39 0 N
例外，jobs运行失败的日志在alert log里面。
DBMS_SCHEDULER
Logging of job runs (job history) --job运行的日志
Simple but powerful scheduling syntax (similar to but more powerful than cron syntax) --更简单强大的语法
Running of jobs outside of the database on the operating system (see below)--运行os命令
Resource management between different classes of jobs--资源管理功能
Use of job arguments including passing of objects into stored procedures--使用任务参数
Privilege-based security model for jobs -- 更好的权限控制
Naming of jobs and comments in jobs --job命名和描述
Stored, reusable schedules --存储和重用存储过程
--常见用法
BEGIN
DBMS_SCHEDULER.create_job (
job_name => ‘test_full_job_definition‘,
job_type => ‘PLSQL_BLOCK‘,
job_action => ‘BEGIN DBMS_STATS.gather_schema_stats(‘‘WJ‘‘); END;‘,
start_date => SYSTIMESTAMP,
repeat_interval => ‘freq=hourly; byminute=0‘,
end_date => NULL,
enabled => TRUE,
comments => ‘Job defined entirely by the CREATE JOB procedure.‘);
END;
运行os命令的例子
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => ‘my_OS_job‘,
job_type => ‘EXECUTABLE‘,
job_action => ‘ls /tmp‘,
repeat_interval => ‘FREQ=MINUTELY‘,
enabled => TRUE
);
END;
--运行一个job
EXEC dbms_scheduler.run_job(‘TEST_FULL_JOB_DEFINITION‘); SELECT * FROM dba_scheduler_jobs WHERE job_name = ‘TEST_FULL_JOB_DEFINITION‘;
REPEAT_INTERVAL的例子
‘FREQ=DAILY; BYDAY=SUN; BYHOUR=18;‘, ‘freq=MINUTELY;interval=1‘, ‘freq=MINUTELY;interval=5‘, ‘freq=HOURLY;interval=1‘, ‘FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI,SAT,SUN; BYHOUR=22;‘,
更多关于dbms_scheduler的用法可以常见官方文档
http://docs.oracle.com/cd/E11882_01/server.112/e25494/scheduse.htm#ADMIN13380
参考文档：
http://docs.oracle.com/cd/B10501_01/server.920/a96521/jobq.htm#9499
http://www.mandsconsulting.com/oracle-dbms_scheduler-vs-dbms_job-create-run-monitor-remove
http://docs.oracle.com/cd/E11882_01/server.112/e25494/scheduse.htm#ADMIN13380