如何使用dbms_job系统包管理job

最新推荐文章于 2024-01-15 17:17:34 发布

原创最新推荐文章于 2024-01-15 17:17:34 发布 · 752 阅读

0 ·

CC 4.0 BY-SA版权

Oracle SQL&PL/SQL 专栏收录该内容

3 篇文章

订阅专栏

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时要注意，作业不能调用作业，作业描述中的单引要用双单引替换