如何使用dbms_job系统包管理job

Oracle为我们提供了一个包，可以用来管理和维护job,下面来做一些相关的测试


通常，一个job有四要素:
jobno 作业号:系统自动产生
what 做什么:由用户来指定,以分号分隔语句,可以是存储过程.
when 什么时间开始做:日期类型
interval 作业的间隔时间:字符型


ZLM@ora10g> create table t1 as select * from scott.emp;


Table created.


ZLM@ora10g> select * from t1;


     EMPNO ENAME      JOB       MGR HIREDATE SAL   COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
      7369 SMITH      CLERK      7902 17-12月-80 800 20
      7499 ALLEN      SALESMAN      7698 20-2月 -81 1600    300 30
      7521 WARD       SALESMAN      7698 22-2月 -81 1250    500 30
      7566 JONES      MANAGER      7839 02-4月 -81 2975 20
      7654 MARTIN     SALESMAN      7698 28-9月 -81 1250   1400 30
      7698 BLAKE      MANAGER      7839 01-5月 -81 2850 30
      7782 CLARK      MANAGER      7839 09-6月 -81 2450 10
      7788 SCOTT      ANALYST      7566 19-4月 -87 3000 20
      7839 KING       PRESIDENT   17-11月-81 5000 10
      7844 TURNER     SALESMAN      7698 08-9月 -81 1500      0 30
      7876 ADAMS      CLERK      7788 23-5月 -87 1100 20
      7900 JAMES      CLERK      7698 03-12月-81 950 30
      7902 FORD       ANALYST      7566 03-12月-81 3000 20
      7934 MILLER     CLERK      7782 23-1月 -82 1300 10


14 rows selected.


--提交一个作业
ZLM@ora10g> VARIABLE jobno NUMBER
BEGIN
DBMS_JOB.SUBMIT(:jobno,'update zlm.t1 set sal=sal+1;',SYSDATE , 'SYSDATE + 1/24/60');
COMMIT;
END;
/


PL/SQL procedure successfully completed.


以上job表示从当前时间开始，每分钟自动将t1表中sal字段值递增1


ZLM@ora10g> col what for a80
ZLM@ora10g> select JOB,WHAT from dba_jobs;


       JOB WHAT
---------- --------------------------------------------------------------------------------
1 EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS();
21 update zlm.t1 set sal=sal+1;


jobno为1的是系统自动运行的job，而jobno为21的这个，就是刚才命令指定的job执行的内容了


ZLM@ora10g> set time on;
14:27:51 ZLM@ora10g> select * from t1;


     EMPNO ENAME      JOB       MGR HIREDATE SAL   COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
      7369 SMITH      CLERK      7902 17-12月-80 802 20
      7499 ALLEN      SALESMAN      7698 20-2月 -81 1602    300 30
      7521 WARD       SALESMAN      7698 22-2月 -81 1252    500 30
      7566 JONES      MANAGER      7839 02-4月 -81 2977 20
      7654 MARTIN     SALESMAN      7698 28-9月 -81 1252   1400 30
      7698 BLAKE      MANAGER      7839 01-5月 -81 2852 30
      7782 CLARK      MANAGER      7839 09-6月 -81 2452 10
      7788 SCOTT      ANALYST      7566 19-4月 -87 3002 20
      7839 KING       PRESIDENT   17-11月-81 5002 10
      7844 TURNER     SALESMAN      7698 08-9月 -81 1502      0 30
      7876 ADAMS      CLERK      7788 23-5月 -87 1102 20
      7900 JAMES      CLERK      7698 03-12月-81 952 30
      7902 FORD       ANALYST      7566 03-12月-81 3002 20
      7934 MILLER     CLERK      7782 23-1月 -82 1302 10


14 rows selected.


14:31:05 ZLM@ora10g> select * from t1;


     EMPNO ENAME      JOB       MGR HIREDATE SAL   COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
      7369 SMITH      CLERK      7902 17-12月-80 806 20
      7499 ALLEN      SALESMAN      7698 20-2月 -81 1606    300 30
      7521 WARD       SALESMAN      7698 22-2月 -81 1256    500 30
      7566 JONES      MANAGER      7839 02-4月 -81 2981 20
      7654 MARTIN     SALESMAN      7698 28-9月 -81 1256   1400 30
      7698 BLAKE      MANAGER      7839 01-5月 -81 2856 30
      7782 CLARK      MANAGER      7839 09-6月 -81 2456 10
      7788 SCOTT      ANALYST      7566 19-4月 -87 3006 20
      7839 KING       PRESIDENT   17-11月-81 5006 10
      7844 TURNER     SALESMAN      7698 08-9月 -81 1506      0 30
      7876 ADAMS      CLERK      7788 23-5月 -87 1106 20
      7900 JAMES      CLERK      7698 03-12月-81 956 30
      7902 FORD       ANALYST      7566 03-12月-81 3006 20
      7934 MILLER     CLERK      7782 23-1月 -82 1306 10


14 rows selected.


可以看到，在4分钟内，sal字段的值已经从2递增到了6


--删除作业
14:31:14 ZLM@ora10g> exec dbms_job.remove(21);


PL/SQL procedure successfully completed.


根据dba_jobs视图中job字段查到的jobno，通过dbms_job包的remove过程，就可以将对应的任务删除


14:32:38 ZLM@ora10g> set time off;
ZLM@ora10g> select job,what from dba_jobs;


       JOB WHAT
---------- --------------------------------------------------------------------------------
1 EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS();
 
此时，我们自定义的那个job已经不见了


--查看后台进程
ZLM@ora10g> col error clear
ZLM@ora10g> select * from v$bgprocess where paddr<>'00';


PADDR   PSERIAL# NAME  DESCRIPTION ERROR
-------- ---------- ----- ---------------------------------------------------------------- ----------
3F216830  1 PMON  process cleanup    0
3F216DE4  1 PSP0  process spawner 0    0
3F217398  1 MMAN  Memory Manager    0
3F21794C  1 DBW0  db writer process 0    0
3F21B808  1 ARC0  Archival Process 0    0
3F21BDBC  1 ARC1  Archival Process 1    0
3F21C370  1 ARC2  Archival Process 2  448
3F217F00  1 LGWR  Redo etc.    0
3F2184B4  1 CKPT  checkpoint    0
3F218A68  1 SMON  System Monitor Process    0
3F21901C  1 RECO  distributed recovery    0
3F2195D0  1 CJQ0  Job Queue Coordinator    0
3F21C924  1 QMNC  AQ Coordinator    0
3F219B84  1 MMON  Manageability Monitor Process    0
3F21A138  1 MMNL  Manageability Monitor Process 2    0


15 rows selected.


注意，这里的CJQ0(Job Queue Coordinator) 就是作业队列调度后台进程
另外，同时最大的作业数是由JOB_QUEUE_PROCESSES这个参数来定义的


ZLM@ora10g> show parameter JOB_QUEUE_PROCESSES


NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes     integer 10


10g中，这个值默认就是10，如果该值为0，那么不会有任何job被调度


--把刚才的作业重新再提交一次
ZLM@ora10g> VARIABLE jobno NUMBER
BEGIN
DBMS_JOB.SUBMIT(:jobno,'update zlm.t1 set sal=sal+1;',SYSDATE , 'SYSDATE + 1/24/60');
COMMIT;
END;
/


--验证作业
ZLM@ora10g> print jobno


     JOBNO
----------
22


ZLM@ora10g> select JOB,WHAT,LAST_DATE,NEXT_DATE from dba_jobs;


       JOB WHAT    LAST_DATE   NEXT_DATE
---------- -------------------------------------------------------------------------------- -------------- --------------
1 EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS();    31-3月 -15   31-3月 -15
22 update zlm.t1 set sal=sal+1;   31-3月 -15   31-3月 -15

jobno已经递增了1，从刚才的21变为22，因为这个作业号是由sys.jobseq这个序列产生，步进为1


ZLM@ora10g> col sequence_owner for a10
ZLM@ora10g> col sequence_name for a10
ZLM@ora10g> select * from dba_sequences where SEQUENCE_NAME='JOBSEQ';


SEQUENCE_O SEQUENCE_N  MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
---------- ---------- ---------- ---------- ------------ - - ---------- -----------
SYS   JOBSEQ       1  999999999       1 Y N     20 41


ZLM@ora10g> select * from DBA_JOBS_RUNNING;


no rows selected


DBA_JOBS_RUNNING这个视图，只有在当前有job正在运行的时候，才会有值，由于我创建的job很简单，运行一瞬间就能完成，因此基本都会返回没有记录


--修改作业调度时间
ZLM@ora10g> col what for a70
ZLM@ora10g> col interval  for a30
ZLM@ora10g> select JOB,WHAT,INTERVAL from dba_jobs;


       JOB WHAT  INTERVAL
---------- ---------------------------------------------------------------------- ------------------------------
1 EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS();  sysdate + 1 / (24 * 60)
22 update zlm.t1 set sal=sal+1;  SYSDATE + 1/24/60

ZLM@ora10g> BEGIN
DBMS_JOB.CHANGE(22, NULL, NULL, 'SYSDATE+5/24/60');
END;
/


PL/SQL procedure successfully completed.


ZLM@ora10g> select JOB,WHAT,INTERVAL from dba_jobs;


       JOB WHAT  INTERVAL
---------- ---------------------------------------------------------------------- ------------------------------
1 EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS();  sysdate + 1 / (24 * 60)
22 update zlm.t1 set sal=sal+1;  SYSDATE+5/24/60


job调度时间由每分钟运行一次改为，每5分钟运行一次


--修改下次启用时间
ZLM@ora10g> BEGIN
DBMS_JOB.NEXT_DATE(22, SYSDATE + 1);
END;
/


PL/SQL procedure successfully completed.


ZLM@ora10g> select job,what,next_date from dba_jobs;


       JOB WHAT  NEXT_DATE
---------- ---------------------------------------------------------------------- --------------
1 EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS();  31-3月 -15
22 update zlm.t1 set sal=sal+1;  01-4月 -15

表示从第2天开始运行


--修改作业运行后不再启用
ZLM@ora10g> BEGIN
DBMS_JOB.INTERVAL(22, 'NULL');
END;
/


PL/SQL procedure successfully completed.

ZLM@ora10g> select * from dba_jobs_running;


       SID  JOB FAILURES LAST_DATE LAST_SEC THIS_DATE THIS_SEC   INSTANCE
---------- ---------- ---------- -------------- ---------------- -------------- ---------------- ----------
       143   22       0 31-3月 -15 23:47:09  0


默认就是从sysdate当前时间开始运行，且只会运行一次，此时查看dba_jobs_running视图，发现是有值的，并且job之后不会再运行了
  
--修改作业的内容
ZLM@ora10g> BEGIN
DBMS_JOB.WHAT(22,'update zlm.t1 set sal=sal+1;update zlm.t1 set sal=sal-2;');
end;
/


PL/SQL procedure successfully completed.


--直接运行作业
ZLM@ora10g> BEGIN
DBMS_JOB.RUN(22);
END;
/


PL/SQL procedure successfully completed.


--中断作业
ZLM@ora10g> BEGIN
DBMS_JOB.BROKEN(22, TRUE);
END;
/


PL/SQL procedure successfully completed.


--重新启用作业
ZLM@ora10g> BEGIN
DBMS_JOB.BROKEN(22, FALSE, SYSDATE);
END;
/


PL/SQL procedure successfully completed.


在使用dbms_job时要注意，作业不能调用作业，作业描述中的单引要用双单引替换