PL/SQL:学会使用DBMS_JOB包

 一、DBMS_JOB包的使用
    DBMS_JOB包用于安排和管理作业队列.通过使用作业,可以使ORACLE数据库定期执行特定的任务.
    注意:使用DBMS_JOB管理作业时,必须确保设置初始化参数JOB_QUEUE_PROCESSES(不能为0)
  1.SUBMIT
    该过程用于建立一个新作业,当建立作业时,需要给出作业要执行的操作,作业的下次运行日期及时间间隔.
    语法:
    DBMS_JOB.SUBMIT(
     job OUT BINARY_INTEGER,
     what IN VARCHAR2,
     next_date IN DATE DEFAULT sysdate,
     interval  IN VARCHAR2 DEFAULT 'null',
     no_parse  IN BOOLEAN DEFAULT FALSE,
     instance  IN BINARY_INTEGER DEFAULT any_instance,
     force     IN BOOLEAN DEFAULT FALSE);
    如上所示,job用于指定作业编号;what用于指定作业要执行的操作;next_date用于指定作业的下次运行日期;
 interval用于指定运行作业的时间间隔;no_parse用于指定是否解析与作业相关的过程;instance用于指定哪个
 例程可以运行作业;force用于指定是否强制运行与作业相关的例程,下面以建立用于分析scott.emp表的作业为
 例,说明建立作业的方法.示例如下:
var jobno number
begin
    dbms_job.submit(
    :jobno,
    'dbms_ddl.analyze_object(''table'',''scott'',''emp'',''compute'');',
     sysdate,
    'sysdate+1');
    commit;
end;
/
jobno
---------
21
以上建立了一个作业,作业号为:21,下面可以进行调用此作业号,运行;

   2.REMOVE
   该过程用于删除作业队列中的特定作业,语法如下:
     DBMS_JOB.REMOVE (job IN BINARY_INTEGER);
   下面以删除作业21为例,说明使用该过程的方法.如下:
     DBMS_JOB.REMOVE(21);
   3.CHANGE
   该过程用于改变与作业相关的所有信息,包括作业操作,作业运行日期以及运行时间间隔等)语法如下:
     DBMS_JOB.CHANGE(
       job IN BINARY_INTEGER,what IN VARCHAR2,
       next_date IN DATE,interval IN VARCHAR2,
       instance  IN BINARY_INTEGER DEFAULT null,
       force IN BOOLEAN DEFAULT FALSE);
   下面以改变作业的
    exec DBMS_JOB.CHANGE(21,null,null,'SYSDATE+2')
   4.WHAT
   该过程用于改变作业要执行的操作,语法如下:
    DBMS_JOB.WHAT(job IN BINARY_INTEGER,what IN VARCHAR2);
   下面以改变作业21的运行操作为例:
    exec dbms_job.what(21,'dbms_stats.gather_table_stats(''scott'',''EMP'');');
   5,NEXT_DATE
   该过程用于改变作业的下次运行日期.语法如下:
      DBMS_JOB.NEXT_DATE (job IN BINARY_INTEGER,next_date IN DATE);
   下面以改变作业21的下次运行日期为例.
      DBMS_JOB.NEXT_DATE(21,'SYSDATE+1');
   6.INSTANCE
   该过程用于改变运行作业的例程,语法如下:
      DBMS_JOB.INSTANCE (
       job IN BINARY_INTEGER,
       instance IN BINARY_INTEGER,
       force IN BOOLEAN DEFAULT FALSE);
   实例:
      DBMS_JOB.INSTANCE(21,1)
   7.INTERVAL
    该过程用于改变作业的运行时间间隔.语法如下:
      DBMS_JOB.INTERVAL(job in BINARY_INTEGER,interval IN VARCHAR2);
    实例:
     exec  DBMS_JOB.INTERVAL(21,'SYSDATE+1/24/60');
   8.BROKEN
     该过程用于设置作业的中断标记,当中断了作业之后.作业将不会被运行.语法如下:
     DBMS_JOB.BROKEN(
     job IN BINARY_INTEGER,broken IN BOOLEAN,
     next_date IN DATE DEFAULT SYSDATE);
   实例:
     exec DBMS_JOB.BROKEN(21,TRUE,'sysdate+1')
   9.RUN
    该过程用于运行已存在的作业.语法如下
     DBMS_JOB.RUN(job iN BINARY_INTEGER,force IN BOOLEAN dEFAULT FALSE);
    实例:
     DBMS_JOB.RUN(21);
   10.作业使用示例:
     当在ORACLE 数据库中使用作业时,应该首先使用过程SUBMIT来建立作业,然后使用过程RUN来运行作业.
   下面以每天搜集SCOTT方案的所有对应统计为例.说明在ORACLE数据库中使用作业的方法.
    a. 创建一个表

SQL> create table T(a date);

    b. 创建一个自定义过程
SQL>create or replace procedure p_t_insert is
         begin
         insert into t values(sysdate);
         end;
         /
    c.创建JOB

SQL> variable job number;
SQL> exec DBMS_JOB.SUBMIT(:job,'p_t_insert;',sysdate,'sysdate+1/1440');--每天1440分钟，即一分钟运行p_t_insert过程一次
SQL> exec DBMS_JOB.RUN(:job);
SQL> select to_char(d,'yyyy/mm/dd hh24:mi:ss') 时间 from T;
时间
-------------------
2013/01/20 16:49:17
2013/01/20 16:50:19
2013/01/20 16:51:24
2013/01/20 16:52:29
2013/01/20 16:53:34

    d.禁止作业运行

SQL>exec DBMS_JOB.BROKEN(:job,ture);

    e.删除作业

SQL>exec DBMS_JOB.REMOVE(:job);

    f.job change//修改作业
SQL>execute dbms_job.change(:job,null,null,'sysdate+3');
SQL>execute dbms_job.change(:job,'scott.test(update)');
     COMMIT;请记得一定要提交；

二、作业的管理

  1.显示当前使用所有作业：

   select job, what, next_date, next_sec, sysdate, failures, broken,interval  from user_jobs a;

   相关视图：dba_jobs、all_jobs、user_jobs、dba_jobs_running 包含正在运行job相关信息
  2.初始化相关参数job_queue_processes
     alter system set job_queue_processes=39 scope=spfile;//最大值不能超过1000 ;job_queue_interval = 10 //调度作业刷新频率秒为单位
     DBA_JOBS describes all jobs in the database.
     USER_JOBS describes all jobs owned by the current user

  3.查询job的情况。
     show paramter background_dump_dest.
    看alter.log 和trace

  4.停其他用户的job
    SQL>exec sys.dbms_ijob.broken(21,true);  －－true停止，false打开
    SQL>commit;


   描述                      INTERVAL参数值                                                         
每天1440分钟，即一分钟运行 sysdate+1/1440
每天午夜12点              'TRUNC(SYSDATE + 1)'
每天早上8点30分          'TRUNC(SYSDATE + 1) + （8*60+30）/(24*60)'
每星期二中午12点          'NEXT_DAY(TRUNC(SYSDATE ), ''TUESDAY'' ) + 12/24'
每个月第一天的午夜12点      'TRUNC(LAST_DAY(SYSDATE ) + 1)'
每个季度最后一天的晚上11点 'TRUNC(ADD_MONTHS(SYSDATE + 2/24, 3 ), 'Q' ) -1/24'
每星期六和日早上6点10分      'TRUNC(LEAST(NEXT_DAY(SYSDATE, ''SATURDAY"), NEXT_DAY(SYSDATE, "SUNDAY"))) + （6×60+10）/（24×60）'

DBA_JOBS 表内容 

字段（列）            类型                         描述
JOB                    NUMBER               任务的唯一标示号
LOG_USER         VARCHAR2(30)      提交任务的用户
PRIV_USER        VARCHAR2(30)      赋予任务权限的用户
SCHEMA_USER  VARCHAR2(30)      对任务作语法分析的用户模式
LAST_DATE         DATE                    最后一次成功运行任务的时间
LAST_SEC          VARCHAR2(8)        如HH24:MM:SS格式的last_date日期的小时，分钟和秒
THIS_DATE          DATE                    正在运行任务的开始时间，如果没有运行任务则为null
THIS_SEC           VARCHAR2(8)        如HH24:MM:SS格式的this_date日期的小时，分钟和秒
NEXT_DATE         DATE                    下一次定时运行任务的时间
NEXT_SEC          VARCHAR2(8)        如HH24:MM:SS格式的next_date日期的小时，分钟和秒
TOTAL_TIME        NUMBER              该任务运行所需要的总时间，单位为秒
BROKEN             VARCHAR2(1)        标志参数，Y标示任务中断，以后不会运行
INTERVAL           VARCHAR2(200)    用于计算下一运行时间的表达式
FAILURES           NUMBER               任务运行连续没有成功的次数
WHAT                 VARCHAR2(2000)   执行任务的PL/SQL块
CURRENT_SESSION_LABEL RAW  MLSLABEL      该任务的信任Oracle会话符
CLEARANCE_HI      RAW MLSLABEL     该任务可信任的Oracle最大间隙
CLEARANCE_LO      RAW                  MLSLABEL 该任务可信任的Oracle最小间隙
NLS_ENV           VARCHAR2(2000)     任务运行的NLS会话设置
MISC_ENV          RAW(32)                 任务运行的其他一些会话参数