Oracle定时任务

一、设置初始化参数 job_queue_processes
　　

 

alter system set job_queue_processes=n;（n>0）

job_queue_processes最大值为1000
　　
　　查看job queue 后台进程（两种方法）

 

1、select name,description from v$bgprocess;
2、show parameter job_queue_processes;

 
　　
二，dbms_job package 用法介绍

1. Broken()过程

 

      更新一个已提交的工作的状态，典型地是用来把一个已破工作标记为未破工作。

PROCEDURE Broken (job    IN binary_integer,
　　　　　　　　　　　   Broken　　IN boolean,
　　　　　　　　　　　   next_date IN date :=SYSDATE)

   　   注:job如果由于某种原因未能成功之行，oracle将重试16次后，还未能成功执行，将被标记为broken重新启动状态为        broken的job，有如下两种方式;

2.change()过程

      用来改变指定工作的设置。

PROCEDURE Change (job　　　　IN binary_integer,
　　　　　　　　　　　          What　　　 IN varchar2,
　　　　　　　　　　　          next_date　IN date,
　　　　　　　　　　　          interval　 IN varchar2)

        
 3.interval()过程

 

      显式地设置重执行一个工作之间的时间间隔数。

PROCEDURE Interval (job　　  IN binary_integer,
　　　　　　　　　　　  interval IN varchar2)

 

 4.Isubmit()过程

 

      用特定的工作号提交一个工作

PROCEDURE ISubmit (job　　　 IN binary_ineger,
　　　　　　　　　　　 What　　　IN varchar2,
　　　　　　　　　　　 next_date IN date,
　　　　　　　　　　　 interval　IN varchar2,
　　　　　　　　　　　 no_parse　IN booean:=FALSE)

 

 　　注: 这个过程与Submit()过程的唯一区别在于此job参数作为IN型参数传递且包括一个由开发者提供的工作号。如果提供的工作号已被使用，将产生一个错误。
　　
　　
5.next_date()过程

 

      显式地设定一个工作的执行时间

 

PROCEDURE Next_Date(job　　　　 IN binary_ineger,
　　　　　　　　　　　　  next_date　 IN date)

 

 

6.remove()过程

 

     删除一个已计划运行的工作。这个过程接收一个参数：

　　PROCEDURE Remove(job IN　binary_ineger);

    注:job参数唯一地标识一个工作。这个参数的值是由为此工作调用Submit()过程返回的job参数的值。已正在运行的工作不能由调用过程序删除。

 

7.run()过程

 

    立即执行一个工作

procedure run(job in binary_integer);

 

    注:job参数唯一地标识一个工作。

 

8.submit()过程

    使用Submit()过程，工作被正常地计划好。

　　PROCEDURE Submit ( job　　　 OUT binary_ineger,
　　　　　　　　　　　 What　　　IN　varchar2,
　　　　　　　　　　　 next_date IN　date,
　　　　　　　　　　　 interval　IN　varchar2,
　　　　　　　　　　　 no_parse　IN　booean:=FALSE);

 　注:

　　job参数是由Submit()过程返回的binary_ineger。这个值用来唯一标识一个工作。

　　what参数是将被执行的PL/SQL代码块。

　　next_date参数指识何时将运行这个工作。

　　interval参数何时这个工作将被重执行。

　　no_parse参数指示此工作在提交时或执行时是否应进行语法分析——TRUE指示此PL/SQL代码在它第一次执行时应进行语法分析，而FALSE指示本PL/SQL代码应立即进行语法分析。

 

 

9.user_export()过程

 

 　  返回一个命令,用来安排一个存在的工作以便此工作能重新提交。

　　PROCEDURE User_Export(job　　　　IN binary_ineger,
　　　　　　　　　　　　　my_call　　IN OUT varchar2);

   注: job参数标识一个安排了的工作。my_call参数包含在它的当前状态重新提交此工作所需要的正文。

 

 

10.what()过程

 

应许在工作执行时重新设置此正在运行的命令。　　

　　PROCEDURE What (job　IN binary_integer,
　　　　　　　　　　what IN OUT varchar2);

 

 

三、查看相关job信息

1、相关视图

      dba_jobs
　　all_jobs
　　user_jobs
　　dba_jobs_running 包含正在运行job相关信息

2、查看相关信息

select job, next_date, next_sec, failures, broken
from dba_jobs;

　   查看正在运行的JOB相关信息

SELECT SID, r.JOB, LOG_USER, r.THIS_DATE, r.THIS_SEC
FROM DBA_JOBS_RUNNING r, DBA_JOBS j
WHERE r.JOB = j.JOB;

 
　　JOB QUEUE LOCK相关信息

　　SELECT SID, TYPE, ID1, ID2
　　FROM V$LOCK
　　WHERE TYPE = 'JQ';

 

四、简单例子

     创建测试表

create table TEST(a date);

 
　  创建一个自定义过程

create or replace procedure MYPROC as
　begin
　　insert into TEST values(sysdate);
　end;

 
　   创建JOB

variable job1 number;
　begin
　　dbms_job.submit(:job1,'MYPROC;',sysdate,'sysdate+1/1440');　　--每天1440分钟，即一分钟运行test过程一次
　end;

 
　　运行JOB

begin
　dbms_job.run(:job1);
end;

 

 

     删除JOB

begin
　dbms_job.remove(:job1);
end;

 

五、关于job运行时间

 

    1:每分钟执行

Interval => TRUNC(sysdate,'mi') + 1/ (24*60);

 

2:每天定时执行。(例如：每天的凌晨1点执行)

Interval => TRUNC(sysdate) + 1 +1/ (24);

 

3:每周定时执行。(例如：每周一凌晨1点执行)

Interval => TRUNC(next_day(sysdate,'星期一'))+1/24

 

    4:每月定时执行。(例如：每月1日凌晨1点执行)

Interval => TRUNC(LAST_DAY(SYSDATE))+1+1/24

 

    5:每季度定时执行。(例如:每季度的第一天凌晨1点执行)

Interval => TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 1/24

 

  6:每半年定时执行。(例如：每年7月1日和1月1日凌晨1点)

Interval => ADD_MONTHS(trunc(sysdate,'yyyy'),6)+1/24

 

   7:每年定时执行。(例如：每年1月1日凌晨1点执行)

 Interval => ADD_MONTHS(trunc(sysdate,'yyyy'),12)+1/24

 


六、JOB为什么不运行？

1.先来了解一下JOB的参数说明：与job相关的参数一个是job_queue_processes，这个是运行JOB时候所起的进程数，当然系统里面JOB大于这个数值后，就会有排队等候的，最小值是0，表示不运行JOB，最大值是36，在OS上对应的进程时SNPn，9i以后OS   上管理JOB的进程叫CJQn。可以使用下面这个SQL确定目前有几个SNP/CJQ在运行：
  

select * from v$bgprocess

   

  这个paddr不为空的snp/cjq进程就是目前空闲的进程，有的表示正在工作的进程。

  另外一个是job_queue_interval，范围在1--3600之间，单位是秒，这个是唤醒JOB的process，因为每次snp运行完他就休息了，需要定期唤醒他，这个值不能太小，太小会影响数据库的性能。

2.诊断：先确定上面这两个参数设置是否正确，特别是第一个参数，设置为0了，所有JOB就不会跑，确认无误后，我们继续向下。

3.使用下面的SQL察看JOB的的broken,last_date和next_date，last_date是指最近一次job运行成功的结束时间，next_date是根据设置的频率计算的下次执行时间，根据这个信息就可以判断JOB上次是否正常，还可以判断下次的时间对不对，
  

select * from dba_jobs

 
  有时候我们发现他的next_date是4000年1月1日，说明job要不就是在running，要不就是状态是break(broken=Y)，如果发现JOB的broken值为Y，找用户了解一下，确定该JOB是否可以broken，如果不能broken，那就把broken值修改成N，修改再使用     上  面的SQL察看就发现他的last_date已经变了，JOB即可正常运行，修改broken状态的SQL如下：

 

declare
   BEGIN
      DBMS_JOB.BROKEN(,FALSE);
   END;

 
4.使用下面的SQL查询是否JOB还在Running
    select * from dba_jobs_running
   如果发现JOB已经Run了很久了还没有结束，就要查原因了。一般的JOB running时会锁定相关的相关的资源，可以查看一下v$access和v$locked_object这两个view，如果发现其他进程锁定了与JOB相关的Object，包括PKG/Function/Procedure/Table等    资源，那么就要把其他进程删除，有必要的话，把JOB的进程也删除，再重新跑看看结果。

5.如果上面都正常，但是JOB还不run，怎么办？那我们要考虑把JOB进程重启一次，防止是SNP进程死了造成JOB不跑，指令如下：

alter system set job_queue_processes=0 --关闭job进程，等待5--10秒钟

alter system set job_quene_processes=5 --恢复原来的值

6.Oracle的BUG

    Oracle9i里面有一个BUG，当计数器到497天时，刚好达到它的最大值，再计数就会变成-1，继续计数就变成0了，然后计数器将不再跑了。如果碰到这种情况就得重启数据库，我们这边有一个生产型的数据库版本是9205，就发生过这样一次问题，后来和用户约   时间重启后就没问题了。但是其他的Oracle7345和Oracle8i的数据库没有发现这个问题。

7.数据库上的检查基本上就这多，如果JOB运行还有问题，那需要配合用户察看一下是否是程序本身的问题，比如处理的资料量大，或者网络速度慢等造成运行时过长，那就需要具体情况具体分析了。我们可以通过下面的SQL手工执行一下JOB。

declare
      begin
         dbms_job.run(_ID)
      end;