本文详细介绍Oracle数据库中DBMS_JOB包的使用方法，包括如何创建、修改、删除任务等关键操作，并通过具体实例演示了各项功能。
oracle job是通过pl/sql DBMS_JOB包来创建和管理的。下面就分别介绍下DBMS_JOB包的各个子过程
1、DBMS_JOB.SUBMIT
作用：This procedure submits a new job. It chooses the job number from the sequencesys.jobseq.
原型：
procedure submit(
job out binary_integer,
what in varchar2,
next_date in date DEFAULT sysdate,
interval in varchar2 DEFAULT 'null',
no_parse in boolean := false
);
job参数是由submit()过程返回的binary_integer;这个值用来唯一标识一个job
what参数是将被执行的pl/sql代码块
next_date参数指示何时将运行这个job, 缺省值是sysdate, 也就是一旦提交成功，马上执行
interval : Date function that calculates the next time to run the job. The default isNULL. This must evaluate to a either a future point in time or
NULL.
no_parse : A flag. The default is FALSE. If this is set to
FALSE, then Oracle parses the procedure associated with the job immediately. If this is set toTRUE, then Oracle parses the procedure associated with the job the first time that the job is run.
注意：在使用DBMS_JOB.SUBMIT过程提交一个job时，必须commit, 否则该job不生效
实验：使用scott用户提交一个job
Last login: Sat Jun 21 10:42:00 2014 from 192.168.137.1
[oracle@redhat4 ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on 星期六 6月 21 13:07:24 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn scott/tiger
Connected.
SQL> create table mydept as select * from dept where rownum < 2;
Table created.
SQL> select * from mydept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
SQL> update mydept set deptno=1 where rownum=1;
1 row updated.
SQL> commit;
Commit complete.
SQL> select * from mydept;
DEPTNO DNAME LOC
---------- -------------- -------------
1 ACCOUNTING NEW YORK
SQL> create procedure insert_mydept
2 as
3 v_maxnum mydept.deptno%TYPE;
4 begin
5 select max(t.deptno) into v_maxnum from mydept t;
6 v_maxnum:=v_maxnum+1;
7 insert into mydept select v_maxnum,t.dname,t.loc from mydept t where rownum < 2;
8 commit;
9 end insert_mydept;
10 /
Procedure created.
-- 测试环境搭建完毕，现在提交job
SQL> print
JOBNO
----------
SQL> exec DBMS_JOB.SUBMIT(:jobno,'insert_mydept;',sysdate,'sysdate+1/2880',false) -- 这里我们没有commit
PL/SQL procedure successfully completed.
SQL> print
JOBNO
----------
3
-- 通过查看变量jobno, 可知我们刚创建的job编号是3， 在当前会话查看查询数据字典user_jobs
SQL> select t.JOB,t.LOG_USER,t.PRIV_USER,t.SCHEMA_USER,t.LAST_DATE,t.NEXT_DATE,t.BROKEN,t.FAILURES,t.WHAT from user_jobs t;
JOB LOG_USER PRIV_USER SCHEMA_USER LAST_DATE NEXT_DATE B FAILURES WHAT
---------- --------------- --------------- ------------------------------ ------------------- ------------------- - ---------- --------------------
3 SCOTT SCOTT SCOTT 2014-06-21 13:28:56 N insert_mydept;
-- 当前会话可以看到新建的job
-- 再开一个窗口，以sys用户查看scott用户的job, 发现scott用户并没有创建job
SQL> conn / as sysdba
Connected.
SQL> select t.JOB,t.LOG_USER,t.PRIV_USER,t.SCHEMA_USER,t.LAST_DATE,t.NEXT_DATE,t.BROKEN,t.FAILURES,t.WHAT from dba_jobs t where t.log_user='SCOTT';
no rows selected
-- 再看看当前数据库是否有活动的事务（我的测试环境事务量少）
SQL> select count(*) from v$transaction;
COUNT(*)
----------
1
-- 发现，有一个活动事务，即刚才scott用户使用DBMS_JOB.SUBMIT过程提交job发起的事务。
-- 切换到刚才cott用户提交job对应的会话， commit
SQL> show user
USER is "SCOTT"
SQL> commit;
Commit complete.
-- 在切换到sys用户，查看scott用户是否含有job
SQL> show user
USER is "SYS"
SQL> select t.JOB,t.LOG_USER,t.PRIV_USER,t.SCHEMA_USER,t.LAST_DATE,t.NEXT_DATE,t.BROKEN,t.FAILURES,t.WHAT from dba_jobs t where t.log_user='SCOTT';
JOB LOG_USER PRIV_USER SCHEMA_USER LAST_DATE NEXT_DATE B FAILURES WHAT
---------- --------------- --------------- ------------------------------ ------------------- ------------------- - ---------- --------------------
3 SCOTT SCOTT SCOTT 2014-06-21 13:28:56 N insert_mydept;
-- 此时，查看表scott.mydept, 发现每隔30s增加一条数据
SQL> select * from mydept;
DEPTNO DNAME LOC
---------- -------------- -------------
1 ACCOUNTING NEW YORK
2 ACCOUNTING NEW YORK
3 ACCOUNTING NEW YORK
4 ACCOUNTING NEW YORK
5 ACCOUNTING NEW YORK
2、DBMS_JOB.RUN
作用：用来立即执行一个指定的job
原型：procedure run(job in binary_integer);
job参数标识将被立即执行的job
实验：将一个停止的job运行起来 (一个已停止的job, 在user_jobs或dba_jobs视图中，next_date=4000-01-01 00:00:00 broken=Y)
SQL> show user
USER is "SCOTT"
SQL> select t.JOB,t.LOG_USER,t.PRIV_USER,t.SCHEMA_USER,t.LAST_DATE,t.NEXT_DATE,t.BROKEN,t.FAILURES,t.WHAT from user_jobs t;
JOB LOG_USER PRIV_USER SCHEMA_USER LAST_DATE NEXT_DATE B FAILURES WHAT
---------- --------------- --------------- ------------------------------ ------------------- ------------------- - ---------- --------------------
3 SCOTT SCOTT SCOTT 2014-06-21 13:52:104000-01-01 00:00:00 Y
0 insert_mydept;
SQL> exec DBMS_JOB.RUN(3)
PL/SQL procedure successfully completed.
SQL> select t.JOB,t.LOG_USER,t.PRIV_USER,t.SCHEMA_USER,t.LAST_DATE,t.NEXT_DATE,t.BROKEN,t.FAILURES,t.WHAT from user_jobs t;
JOB LOG_USER PRIV_USER SCHEMA_USER LAST_DATE NEXT_DATE B FAILURES WHAT
---------- --------------- --------------- ------------------------------ ------------------- ------------------- - ---------- --------------------
3 SCOTT SCOTT SCOTT 2014-06-21 14:00:27 2014-06-21 14:00:57 N 0 insert_mydept;
3、 DBMS_JOB.BROKEN
作用：This procedure sets the broken flag. Broken jobs are never run.
原型：
procedure broken
(
job in binary_integer,
broken in boolean,
next_date in date := SYSDATE
);
job参数是job number，它唯一标识job。
broken参数指示此job是否将标记为broken（true说明此job将标记为broken，而false说明此job将标记为not broken）
next_date参数指示在什么时候此工作将再次运行。此参数缺省值为当前日期和时间。
注意：1. 在使用broken过程设置job的broken标志位时，需要commit
2. If you set job as broken while it is running, Oracle resets the job's status to normal after the job completes. Therefore, only execute this procedure for jobs that are not running.
3. job如果由于某种原因未能成功执行，若oracle在重试16次后，还未能成功执行，将被标记为broken，重新启动状态为broken的job,有以下两种方式：
a. 利用dbms_job.run()立即执行该job
sql>begin
sql>dbms_job.run(:jobno); --该jobno为submit过程提交时返回的job number
sql>end;
sql>/
b. 利用dbms_job.broken()重新将broken标记为false
sql>begin
sql>dbms_job.broken(:job,false,next_date);
sql>commit;
sql>end;
sql>/
4、DBMS_JOB.CHANGE
作用：用来改变指定job的设置
原型：
procedure change(
job in binary_integer,
what in varchar2,
next_date in date,
interval in varchar2
);
此job参数是一个整数值，它唯一标识一个job。
what参数是由此job运行的一块pl/sql代码块。
next_date参数指示何时此job将被执行。
interval参数指示一个job重执行的频度。
实验：改变一个job的what字段
SQL> show user
USER is "SCOTT"
SQL> select t.JOB,t.LOG_USER,t.PRIV_USER,t.SCHEMA_USER,t.LAST_DATE,t.NEXT_DATE,t.BROKEN,t.FAILURES,t.WHAT from user_jobs t
2 /
JOB LOG_USER PRIV_USER SCHEMA_USER LAST_DATE NEXT_DATE B FAILURES WHAT
---------- --------------- --------------- ------------------------------ ------------------- ------------------- - ---------- --------------------
3 SCOTT SCOTT SCOTT 2014-06-21 20:13:51 4000-01-01 00:00:00 Y 0insert_mydept;
SQL> begin
2 dbms_job.change(3,'insert_mydept(10);',null,null);
3 commit;
4 end;
5 /
PL/SQL procedure successfully completed.
SQL> select t.JOB,t.LOG_USER,t.PRIV_USER,t.SCHEMA_USER,t.LAST_DATE,t.NEXT_DATE,t.BROKEN,t.FAILURES,t.WHAT from user_jobs t;
JOB LOG_USER PRIV_USER SCHEMA_USER LAST_DATE NEXT_DATE B FAILURES WHAT
---------- --------------- --------------- ------------------------------ ------------------- ------------------- - ---------- --------------------
3 SCOTT SCOTT SCOTT 2014-06-21 20:13:51 4000-01-01 00:00:00 Y 0insert_mydept(10);
注意：1. You must issue a COMMIT statement immediately after the statement.
2. If the parameters what, next_date, orinterval are
NULL, then leave that value as it is.
5、DBMS_JOB.INTERVAL
作用：This procedure changes how often a job runs.
原型：
procedure interval(
job in binary_integer,
interval in varchar2
);
job参数标识一个特定的job；
interval参数指定一个job重执行的频度。
注意：
1.If the job completes successfully, then this new date is placed innext_date.
interval is evaluated by plugging it into the statement selectinterval into
next_date from dual;
2. The interval parameter must evaluate to a time in the future.
3. If interval evaluates to NULL and if a job completes successfully, then the job is automatically deleted from the queue.
4. You must issue a COMMIT statement immediately after the statement.
6、DBMS_JOB.NEXT_DATE
作用：This procedure changes when an existing job next runs.
原型：procedure next_date(
job in binary_integer,
next_date in date
);
注意：You must issue a COMMIT statement immediately after the statement.
实验：改变一个job的next_date字段
[oracle@redhat4 ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on 星期日 6月 22 09:59:14 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn scott/tiger
Connected.
SQL> show user
USER is "SCOTT"
SQL> print
SP2-0568: No bind variables declared.
SQL> var jobno number
SQL> begin
2 dbms_job.submit(:jobno,'INSERT_MYDEPT(3);',trunc(sysdate+1),'sysdate+1');
3 commit;
4 end;
5 /
PL/SQL procedure successfully completed.
SQL> print
JOBNO
----------
4
SQL> col log_user format a10
SQL> col priv_user format a10
SQL> col schema_user format a10
SQL> col what format a20
SQL> col interval format a15
SQL> select t.JOB,t.LOG_USER,t.PRIV_USER,t.SCHEMA_USER,t.LAST_DATE,t.INTERVAL,t.NEXT_DATE,t.BROKEN,t.FAILURES,t.WHAT from user_jobs t;
JOB LOG_USER PRIV_USER SCHEMA_USE LAST_DATE INTERVAL NEXT_DATE B FAILURES WHAT
---------- ---------- ---------- ----------------- ---------------------- ------------------- - ---------- ------------------- - - ------
4 SCOTT SCOTT SCOTT sysdate+1 2014-06-23 00:00:00 N INSERT_MYDEPT(3);
SQL> begin
2 dbms_job.next_date(4,trunc(sysdate+2));
3 commit;
4 end;
5 /
PL/SQL procedure successfully completed.
SQL> select t.JOB,t.LOG_USER,t.PRIV_USER,t.SCHEMA_USER,t.LAST_DATE,t.INTERVAL,t.NEXT_DATE,t.BROKEN,t.FAILURES,t.WHAT from user_jobs t;
JOB LOG_USER PRIV_USER SCHEMA_USE LAST_DATE INTERVAL NEXT_DATE B FAILURES WHAT
---------- ---------- ---------- ----------------- ---------------------- ------------------- - ---------- ------------------- - - ------
4 SCOTT SCOTT SCOTT sysdate+1 2014-06-24 00:00:00
N INSERT_MYDEPT(3);
OK，4号job下一次执行时间变成了2014-06-24 00:00:00
7、DBMS_JOB.REMOVE
作用：This procedure removes an existing job from the job queue. This currently does not stop a running job.
原型：procedure remove(
job in binary_integer
);
注意：
1.You must issue a COMMIT statement immediately after the statement.
2.已正在运行的job不能由调用此过程删除。
实验：
-- 查看当前scott用户含有的job
SQL> select t.JOB,t.LOG_USER,t.PRIV_USER,t.SCHEMA_USER,t.LAST_DATE,t.INTERVAL,t.NEXT_DATE,t.BROKEN,t.FAILURES,t.WHAT from user_jobs t;
JOB LOG_USER PRIV_USER SCHEMA_USE LAST_DATE INTERVAL NEXT_DATE B FAILURES WHAT
---------- ---------- ---------- ---------- ------------------- --------------- ------------------- - ---------- --------------------
4 SCOTT SCOTT SCOTT sysdate+1 2014-06-24 00:00:00 N INSERT_MYDEPT(3);
-- 删除4号job
SQL> begin
2 dbms_job.remove(4);
3 commit;
4 end;
5 /
PL/SQL procedure successfully completed.
SQL> select t.JOB,t.LOG_USER,t.PRIV_USER,t.SCHEMA_USER,t.LAST_DATE,t.INTERVAL,t.NEXT_DATE,t.BROKEN,t.FAILURES,t.WHAT from user_jobs t;
no rows selected
8、DBMS_JOB.USER_EXPORT
作用：produces the text of a call to re-create the given job.
原型：procedure user_export(job in binary_integer,my_call out varchar2);
job参数唯一的标志一个job
my_call参数包含在job的当前状态重新提交此job所需的正文(命令)
实验1：未broken的job
SQL> var jobno number
SQL> var my_call varchar2(1024)
SQL> print
JOBNO
----------
MY_CALL
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL> begin
2 dbms_job.submit(:jobno,'INSERT_MYDEPT(3);',trunc(sysdate+1),'sysdate+1');
3 commit;
4 end;
5 /
PL/SQL procedure successfully completed.
SQL> print
JOBNO
----------
5
MY_CALL
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL> select t.JOB,t.LOG_USER,t.PRIV_USER,t.SCHEMA_USER,t.LAST_DATE,t.INTERVAL,t.NEXT_DATE,t.BROKEN,t.FAILURES,t.WHAT from user_jobs t;
JOB LOG_USER PRIV_USER SCHEMA_USE LAST_DATE INTERVAL NEXT_DATE B FAILURES WHAT
---------- -------------- -------------- -------------- ------------------- --------------- ------------------- ------ ---------- --------------------
5 SCOTT SCOTT SCOTT sysdate+1 2014-06-23 00:00:00 N INSERT_MYDEPT(3);
SQL> begin
2 dbms_job.user_export(5,:my_call);
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> print
JOBNO
----------
5
MY_CALL
------------------------------------------------------------------------------------------------------------------------------------------------------
dbms_job.isubmit(job=>5,what=>'INSERT_MYDEPT(3);',next_date=>to_date('2014-06-23:00:00:00','YYYY-MM-DD:HH24:MI:SS'),interval=>'sysdate+1',no_parse=>TRUE);
-- 上面的红色字体就是重新提交此job所需的命令，当然不要忘了commit
实验2：broken的job
SQL> select t.JOB,t.LOG_USER,t.PRIV_USER,t.SCHEMA_USER,t.LAST_DATE,t.INTERVAL,t.NEXT_DATE,t.BROKEN,t.FAILURES,t.WHAT from user_jobs t;
JOB LOG_USER PRIV_USER SCHEMA_USE LAST_DATE INTERVAL NEXT_DATE B FAILURES WHAT
---------- -------------- ---------------- ------------------- --------------- ------------------- ------------------------------ ----- --------------- ------------------------------
5 SCOTT SCOTT SCOTT sysdate+1 4000-01-01 00:00:00 Y INSERT_MYDEPT(3);
SQL> begin
2 dbms_job.user_export(5,:my_call);
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> print my_call
MY_CALL
------------------------------------------------------------------------------------------------------------------------------------------------------
dbms_job.isubmit(job=>5,what=>'INSERT_MYDEPT(3);',next_date=>to_date('4000-01-01:00:00:00','YYYY-MM-DD:HH24:MI:SS'),interval=>'sysdate+1',no_parse=>TRUE);
-- 上面的红色字体就是重新提交此broken job所需的命令。有一个疑问：已经broken的job,利用上面导出的命令，重新提交时，那它的broken标志位是Y, 还是N？
实验3：删除掉上面的5号job,利用user_export导出的命令重新提交此job,查看broken标志位
SQL> begin
2 dbms_job.remove(5);
3 commit;
4 end;
5 /
PL/SQL procedure successfully completed. -- 5号job已被删除
SQL> select t.JOB,t.LOG_USER,t.PRIV_USER,t.SCHEMA_USER,t.LAST_DATE,t.INTERVAL,t.NEXT_DATE,t.BROKEN,t.FAILURES,t.WHAT from user_jobs t;
no rows selected
SQL> begin
2 dbms_job.isubmit(job=>5,what=>'INSERT_MYDEPT(3);',next_date=>to_date('4000-01-01:00:00:00','YYYY-MM-DD:HH24:MI:SS'),interval=>'sysdate+1',no_parse=>TRUE); --利用导出的命令重新提交5号job
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> commit; -- 这里不要忘了commit
Commit complete.
SQL> select t.JOB,t.LOG_USER,t.PRIV_USER,t.SCHEMA_USER,t.LAST_DATE,t.INTERVAL,t.NEXT_DATE,t.BROKEN,t.FAILURES,t.WHAT from user_jobs t;
JOB LOG_USER PRIV_USER SCHEMA_USE LAST_DATE INTERVAL NEXT_DATE B FAILURES WHAT
---------- ---------- ---------- ---------- ------------------- --------------- ----------------------- ---- -------------- --------------------
5 SCOTT SCOTT SCOTT sysdate+1 4000-01-01 00:00:00
N INSERT_MYDEPT(3);
-- 我们发现，broken job 通过user_export命令导出再重新提交时，它的broken标志位变成了N,也就是非 broken, 只不过下一次执行时间变成了4000-01-01 00:00:00 ,这和broken job没什么区别
扫一扫
167
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
