[大数据]Oracle 定时任务job使用详解

Oracle 定时任务job使用详解

1、定时任务job

job在指定的时间点或每天的某个时间点自行执行任务。

关键性能视图

SQL> desc user_jobs;
Name Type Nullable Default Comments

JOB NUMBER Identifier of job. Neither import/export nor repeated executions change it.
LOG_USER VARCHAR2(30) USER who was logged in when the job was submitted
PRIV_USER VARCHAR2(30) USER whose default privileges apply to this job
SCHEMA_USER VARCHAR2(30) select * from bar means select * from schema_user.bar
LAST_DATE DATE Y Date that this job last successfully executed
LAST_SEC VARCHAR2(8) Y Same as LAST_DATE. This is when the last successful execution started.
THIS_DATE DATE Y Date that this job started executing (usually null if not executing)
THIS_SEC VARCHAR2(8) Y Same as THIS_DATE. This is when the last successful execution started.
NEXT_DATE DATE Date that this job will next be executed
NEXT_SEC VARCHAR2(8) Y Same as NEXT_DATE. The job becomes due for execution at this time.
TOTAL_TIME NUMBER Y Total wallclock time spent by the system on this job, in seconds
BROKEN VARCHAR2(1) Y If Y, no attempt is being made to run this job. See dbms_jobq.broken(job).
INTERVAL VARCHAR2(200) A date function, evaluated at the start of execution, becomes next NEXT_DATE
FAILURES NUMBER Y How many times has this job started and failed since its last success?
WHAT VARCHAR2(4000) Y Body of the anonymous PL/SQL block that this job executes
NLS_ENV VARCHAR2(4000) Y alter session parameters describing the NLS environment of the job
MISC_ENV RAW(32) Y a versioned raw maintained by the kernel, for other session parameters
INSTANCE NUMBER Y Instance number restricted to run the job

字段（列）          类型         描述
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_LABELRAW     MLSLABEL 该任务的信任Oracle会话符
CLEARANCE_HI     RAW MLSLABEL   该任务可信任的Oracle最大间隙
CLEARANCE_LO     RAW       MLSLABEL 该任务可信任的Oracle最小间隙
NLS_ENV        VARCHAR2(2000)  任务运行的NLS会话设置
MISC_ENV       RAW(32)     任务运行的其他一些会话参数

2、定时任务测试

(1) 建立一个测试表

SQL> create TABLE TB_JOB_TEST(TS timestamp,JOBSN NUMBER);

Table created

job测试表两个字段：时间戳，执行顺序号

（2）建立一个job调用的存储过程

create or replace procedure pro_job_test is
  i number;
begin

  select count(*) into i from tb_job_test;
  if i=0 then
     i:=i+1;
     insert into tb_job_test values (systimestamp,i);
  else
    select max(jobsn) into i from tb_job_test;
    i:=i+1;
    insert into tb_job_test values (systimestamp,i);
  end if;
    commit;
end;

（3）创建job任务

DECLARE
  JOB NUMBER;
BEGIN
  DBMS_JOB.SUBMIT(JOB,'PRO_JOB_TEST;',SYSDATE,'sysdate+1/24/60');
  COMMIT;
END;
/

为了看效果，1分钟运行一次定时任务。sysdate为了定时任务即可生效。

注意：一定要commit ，否则在user_jobs中不存在。

SQL> select * from TB_JOB_TEST ORDER BY TS;

TS                                  JOBSN
------------------------------ ----------
2021-23-10 02:12:25:096070              1
2021-23-10 02:13:25:284568              2
2021-23-10 02:14:25:453914              3
2021-23-10 02:15:25:628216              4
2021-23-10 02:16:25:903952              5
2021-23-10 02:17:26:073790              6

6 rows selected

user_jobs有ID205的定时任务：

SQL> select * from user_jobs;

       JOB LOG_USER                       PRIV_USER                      SCHEMA_USER                    LAST_DATE   LAST_SEC         THIS_DATE   THIS_SEC         NEXT_DATE   NEXT_SEC         TOTAL_TIME BROKEN INTERVAL                                                                           FAILURES WHAT                                                                             NLS_ENV                                                                          MISC_ENV                                                           INSTANCE
---------- ------------------------------ ------------------------------ ------------------------------ ----------- ---------------- ----------- ---------------- ----------- ---------------- ---------- ------ -------------------------------------------------------------------------------- ---------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------------------------------------------------------- ----------
       205 TEST                           TEST                           TEST                           2021-10-23  02:12:25                                      2021-10-23  02:13:25                  0 N      sysdate+1/24/60                                                                           0 PRO_JOB_TEST;                                                                    NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENC 0102000200000000                                                          0

（4）dbms_job.submit方法

dbms_job.submit方法过程，过程有五个参数：job、what、next_date、interval与no_parse。

 dbms_job.submit(
job    OUT binary_ineger,
What   IN varchar2,
next_date IN date,
interval IN varchar2,
no_parse IN booean:=FALSE)
举例：
declare
    job number;
begin
    dbms_job.submit(job => :job,
    what => 'prc_name;',                          --执行的存储过程的名字
    next_date => to_date('2021-02-02 22:22:22', 'yyyy-mm-dd hh24:mi:ss'),
    interval =>'sysdate+10/86400');            --每天86400秒钟，即十秒钟运行prc_name过程一次
    commit;
end;

job参数是输出参数，由submit()过程返回的binary_ineger，值用来唯一标识一个定时任务。一般定义一个变量接收，可以去user_jobs视图查询job值。

what参数是将被执行的PL/SQL代码块，存储过程名称等。注意：存储过程后必须有冒号：

next_date参数指识何时将运行定时任务。

interval参数何时定时任务将被重执行。

no_parse参数指示此定时任务在提交时或执行时是否应进行语法分析——true，默认值false。指示此PL/SQL代码在它第一次执行时应进行语法分析，而FALSE指示本PL/SQL代码应立即进行语法分析。

（5）INTERVAL设置方法

INTERVAL参数值方法

每天午夜12点 TRUNC(SYSDATE + 1)

每天早上8点30分 TRUNC(SYSDATE + 1) +（860+30）/(2460)

每星期二中午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）

每秒钟执行次 Interval => sysdate+ 1/(24 * 60 * 60)

如果改成sysdate + 10/(24 *60 * 60)就是10秒钟执行次

每分钟执行 Interval =>TRUNC(sysdate,‘mi’) + 1/ (24*60)

如果改成TRUNC(sysdate,‘mi’)+ 10/ (24*60) 就是每10分钟执行次

每天定时执行

? 例如：每天的凌晨1点执行

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

每周定时执行

? 例如：每周一凌晨1点执行

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

每月定时执行

? 例如：每月1日凌晨1点执行

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

每季度定时执行

? 例如每季度的第一天凌晨1点执行

? Interval =>TRUNC(ADD_MONTHS(SYSDATE,3),‘Q’) + 1/24

每半年定时执行

? 例如：每年7月1日和1月1日凌晨1点

? Interval =>ADD_MONTHS(trunc(sysdate,‘yyyy’),6)+1/24

每年定时执行

? 例如：每年1月1日凌晨1点执行

? Interval=>ADD_MONTHS(trunc(sysdate,‘yyyy’),12)+1/24

3、定时任务管理

在dbms_job package中还有其他的过程：broken、change、interval、isubmit、next_date、remove、run、submit、user_export、what；

（1）broken

broken()过程更新一个已提交的定时任务的状态，典型地是用来把一个已破定时任务标记为未破定时任务。过程有三个参数：job、broken与next_date。

? procedure broken (

? job IN binary_integer,

? broken IN boolean,

? next_date IN date := SYSDATE

? )

job参数是定时任务号，它在问题中唯一标识定时任务。

broken参数指示此定时任务是否将标记为破——true说明此定时任务将标记为破，而false说明此定时任务将标记为未破。

next_date参数指示在什么时候此定时任务将再次运行。此参数缺省值为当前日期和时间。

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

a、利用dbms_job.run()立即执行该job

? begin

? dbms_job.run(:job) --该job为submit过程提交时返回的jobnumber或是去dba_jobs去查找对应job的编号

? end;

b、利用dbms_job.broken()重新将broken标记为false

? begin

? dbms_job.broken (:job, false, next_date)

? end;

（2） change

change()过程用来改变指定job的设置。

过程有四个参数：job、what、next_date、interval。

? procedure change (

? job IN binary_integer,

? what IN varchar2,

? next_date IN date,

? interval IN varchar2

? )

job参数是一个整数值，它唯一标识此定时任务。

what参数是由此job运行的一块PL/SQL代码块。

next_date参数指示何时此job将被执行。

interval参数指示一个job重执行的频度。

（3） interval

interval()过程用来显式地设置重复执行一个job之间的时间间隔数。

过程有两个参数：job、interval。

? procedure interval(

? job IN binary_integer,

? interval IN varchar2

? )

job参数标识一个特定的定时任务。

interval参数指示一个定时任务重执行的频度。

（4）isubmit

isubmit()过程用来用特定的job号提交一个job。

过程有五个参数：job、what、next_date、interval、no_parse。

? procedure isubmit (

? job IN binary_ineger,

? what IN varchar2,

? next_date IN date,

? interval IN varchar2,

? no_parse IN booean := FALSE

? )

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

（5）next_date

next_date()过程用来显式地设定一个job的执行时间。过程接收两个参数：job、next_date。

procedure next_date(

? job IN binary_ineger,

? next_date IN date

? )

job标识一个已存在的定时任务。

next_date参数指示了此job应被执行的日期、时间。

（6）remove

remove()过程来删除一个已计划运行的job。过程接收一个参数：

? procedure remove(job INbinary_ineger);

job参数唯一地标识一个定时任务参数的值是由为此定时任务调用submit()过程返回的job参数的值，已正在运行的job不能删除。

（7） run

可以手工执行job。

run()过程用来立即执行一个指定的job。过程只接收一个参数：

procedure run(job INbinary_ineger)

job参数标识将被立即执行的定时任务。

（8）submit

使用submit()过程，job被正常地计划。

前面创建job有详细讲解。

（9）user_export

user_export()过程返回一个命令，此命令用来安排一个存在的job以便此job能重新提交。此程序有两个参数：job、my_call。

procedure user_export(

job IN binary_ineger,

my_call IN OUT varchar2

)

job参数标识一个安排了的定时任务。

my_call参数包含在它的当前状态重新提交此job所需要的正文。

（10）what

what()过程应许在job执行时重新设置此正在运行的命令。过程接收两个参数：job、what。

? procedure what (

? job IN binary_ineger,

? what IN OUT varchar2

? )

job参数标识一个存在的定时任务。

what参数指示将被执行的新的PL/SQL代码。实现的功能：每隔一分钟自动向getSysDate表中插入当前的系统时间。

（11）remove

删除一个job

select * fromuser_jobs; 中的job ID值对应，看what对应的过程

begin

? dbms_job.remove(203);

end;

4、job任务数量并发

初始化相关参数job_queue_processes

SQL> show parameter job_queue_processes ;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     100

SQL> 

调整并发数量：

alter system set job_queue_processes = 200 scope = spfile;

当job_queue_process值为0时表示全部停止oracle的job，可以通过语句

alter system setjob_queue_processes = 10;

来调整启动oracle的job。 如果将job_queue_processes的值设置为1的话，那就是串行运行，即快速切换执行一个job任务。

5、job不执行排查（网上）

首先执行：

SQL> exec SYS.DBMS_ijob.set_enabled(true);

PL/SQL procedure successfully completed

设置的JOB就是不运行，按网上的检查步骤：

下面提供一个checklist用于检查job异常的原因：

1) 实例的RESTRICTED SESSIONS mode?
Check if the instance is in restricted sessions mode:

```
　　SQL> 　　select instance_name,logins from v$instance;
　　
　　INSTANCE_NAME LOGINS

oracledb ALLOWED
　　
　　Executed in 0.06 seconds
　　
　　```
　　
　　如果 logins=RESTRICTED，可以执行：
　　alter system disable restricted session;

2. JOB_QUEUE_PROCESSES=0
   必须 job_queue_processes > 0

   SQL> show parameter job_queue_processes
   NAME                                 TYPE        VALUE
   ------------------------------------ ----------- ------------------------------
   job_queue_processes                  integer     100
   

3. _SYSTEM_TRIG_ENABLED=FALSE
   Check if _system_enabled_trigger=false
   　　col parameter format a25
   　　col value format a15
   　　select a.ksppinm parameter,b.ksppstvl value from x$ksppia,x$ksppcv b
   　　where a.indx=b.indx and ksppinm=’_system_trig_enabled’;
   　　If _system_trig_enabled=false, then
   　　alter system set “_system_trig_enabled”=TRUE scope=both;
   　　^– Checked!

   未验证

4. Is the job BROKEN?
   select job,broken from dba_jobs where job=;
   　　If broken, then check the alert log and trace files to diagnose the issue.
   　　^– Checked! The job is not broken.

   可验证

5. Is the job COMMITted?
   确认job必须有commit ，提交成功:

   DECLARE
     JOB NUMBER;
   BEGIN
   
     DBMS_JOB.SUBMIT(  
           JOB => JOB,  /*自动生成JOB_ID*/  
           WHAT => 'PROC_FRESH_MV;',  /*需要执行的存储过程名称或SQL语句*/  
           NEXT_DATE => SYSDATE+3/(24*60),  /*初次执行时间-下一个3分钟*/  
           INTERVAL => 'TRUNC(SYSDATE,''MI'')+1/(24*60)' /*每隔1分钟执行一次*/
         );  
     COMMIT;
   END;
   /
   

   如果没有，可以强制执行，job号可以在user_jobs中查到：

   ? exec dbms_jobs.run(jobno);

6. UPTIME > 497 days
   Check if the server (machine) has been up for more than 497 days:
   　　For SUN, use ‘uptime’ OS command.
   　　If uptime>497 and the jobs do not execute automatically, then you are hitting unpublished bug 3427424
   　　(Jobs may stop running after 497 days uptime) which is fixed in 9206 and A102
   　　^– Checked! The server in this case has been up 126 days only

   未验证

7. DBA_JOBS_RUNNING
   检查是否一直有job运行中:

   　　select * from dba_jobs_running;
   

   ^– Checked! The job is not running.
   　　LAST_DATE and NEXT_DATE
   　　Check if the last_date and next_date for the job are proper:
   　　select Job,Next_date,Last_date from dba_jobs where job=;
   　　^– NEXT_DATE is porper, however LAST_DATE is null since the job never executes automatically.

8. NEXT_DATE and INTERVAL
   Check if the Next_date is changing properly as per the interval set in dba_jobs:
   　　select Job,Interval,Next_date,Last_date from dba_jobs where job=;
   　　^– This is not possible since the job never gets executed automatically.

9. Toggle value for JOB_QUEUE_PROCESSES
   　　Stop and restart CJQ process(es)
   　　alter system set job_queue_processes=0 ;
   　　–
   　　alter system set job_queue_processes=4 ;
   　　Ref: Bug 2649244 (fixed by: 9015, 9203, 10201)
   　　^– Done but did not help

10. DBMS_IJOB(Non-documented):
    　　Last ditch effort.
    　　Either restart the database or try the following:
    　　exec dbms_ijob.set_enabled(true);
    　　Ref: Bug 3505718 (Closed, Not a Bug)
    　　Done but did not help
    　　These are the most common causes for this behavior.
    　　Solution
    　　The solution ended up to be the server (machine) uptime.
    　　Even though it was up for only 126 days, after the server was rebooted all jobs were able to execute automatically.
    　　To implement the solution, please execute the following steps:

11. Shutdown all applications, including databases.

12. Shutdown the server (machine)

13. Restart all applications, including databases.

14. Check that jobs are executing automatically.
    from metalink docs ： 313102.1

6、JOB锁处理方法

找出正在执行的JOB编号及其会话编号，DBA_JOBS_RUNNING ，没有USER_JOBS_RUNNING!

SQL> 　SELECT  SID,JOB  FROM  DBA_JOBS_RUNNING;

       SID        JOB
---------- ----------
        23        203
        44        204

查找会话的ID：

SQL> SELECT  A.SID,A.JOB,B.SERIAL# FROM  DBA_JOBS_RUNNING A ,V$SESSION B WHERE A.SID=B.SID;

       SID        JOB    SERIAL#
---------- ---------- ----------
        44        204        141
        23        203        387

kill会话

SQL> SELECT 'ALTER  SYSTEM  KILL  SESSION ' || ''''|| A.SID ||','|| B.SERIAL# ||''';' COMMAND FROM  DBA_JOBS_RUNNING A ,V$SESSION B WHERE A.SID=B.SID;

COMMAND
--------------------------------------------------------------------------------
ALTER  SYSTEM  KILL  SESSION '44,141';
ALTER  SYSTEM  KILL  SESSION '23,387';
--把命令拷贝粘贴执行：
SQL> ALTER  SYSTEM  KILL  SESSION '44,141';

System altered


SQL> ALTER  SYSTEM  KILL  SESSION '23,387';

System altered