Oracle Job用法小结

Oracle JOB 用法小结

一、设置初始化参数job_queue_processes

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

job_queue_processes最大值为1000

查看job queue 后台进程

sql>select name,description from v$bgprocess;

二，dbms_job package 用法介绍

包含以下子过程：

Broken()过程。

change()过程。

Interval()过程。

Isubmit()过程。

Next_Date()过程。

Remove()过程。

Run()过程。

Submit()过程。

User_Export()过程。

What()过程。

1、Broken()过程更新一个已提交的工作的状态，典型地是用来

把一个已破工作标记为未破工作。

这个过程有三个参数：job 、broken与next_date。

PROCEDURE Broken (job IN binary_integer,

Broken IN boolean,

next_date IN date :=SYSDATE) job参数是工作号，它在问题中唯一标识工作。

broken参数指示此工作是否将标记为破——TRUE说明此工作将标记为破，而FLASE说明此工作将标记为未破。

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

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

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

sql>begin

sql>dbms_job.run(:jobno) 该jobno为submit过程提交时返回的job number

sql>end;

sql>/

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

sql>begin

sql>dbms_job.broken (:job,false,next_date)

sql>end;

sql>/

2、Change()过程用来改变指定工作的设置。

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

PROCEDURE Change (job IN binary_integer,

What IN varchar2,

next_date IN date,

interval IN varchar2)

此job参数是一个整数值，它唯一标识此工作。

What参数是由此工作运行的一块PL/SQL代码块。

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

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

3、Interval()过程用来显式地设置重执行一个工作之间的时间间隔数。这个过程有两个参数：job与interval。

PROCEDURE Interval (job IN binary_integer,

Interval IN varchar2)

job参数标识一个特定的工作。interval参数指示一个工作重执

行的频度。

4、ISubmit()过程用来用特定的工作号提交一个工作。这个过程有五个参数：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 型参数传递且包括一个由开发者提供的工作号。如果提供的工作号已被使用，将产生一个错误。

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

PROCEDURE Next_Date(job IN

binary_ineger,

next_date IN date)

job标识一个已存在的工作。next_date参数指示了此工作应被执行的日期与时间。

6、Remove()过程来删除一个已计划运行的工作。这个过程接

收一个参数：

PROCEDURE Remove(job IN binary_ineger);

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

7、Run()过程用来立即执行一个指定的工作。这个过程只接收一个参数：

PROCEDURE Run(job IN binary_ineger)

job参数标识将被立即执行的工作。

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

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

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代码块。中国网管论坛https://www.wendangku.net/doc/7c8104508.html,

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

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

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

9、User_Export()过程返回一个命令，此命令用来安排一个存在的工作以便此工作能重新提交。

此程序有两个参数：job与my_call。

PROCEDURE User_Export(job IN

binary_ineger,

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

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

PROCEDURE What (job IN binary_ineger,

What IN OUT varchar2)

job参数标识一个存在的工作。what参数指示将被执行的新的PL/SQL代码。

三、查看相关job信息

1、相关视图

dba_jobs

all_jobs

user_jobs

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

https://www.wendangku.net/doc/7c8104508.html,

2、查看相关信息

SQL>SELECT JOB, NEXT_DATE, NEXT_SEC, FAILURES, BROKEN

SQL>FROM DBA_JOBS;

JOB NEXT_DATE NEXT_SEC FAILURES B

------- --------- -------- -------- -

9125 01-JUN-01 00:00:00 4 N

14144 24-OCT-01 16:35:35 0 N

9127 01-JUN-01 00:00:00 16 Y

3 rows selected.

正在运行的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;

SID JOB LOG_USER THIS_DATE THIS_SEC

----- ---------- ------------- --------- --------

12 14144 HR 24-OCT-94 17:21:24

25 8536 QS 24-OCT-94 16:45:12

2 rows selected.

JOB QUEUE LOCK相关信息

SELECT SID, TYPE, ID1, ID2

FROM V$LOCK

WHERE TYPE = 'JQ';

SID TY ID1 ID2

--------- -- --------- ---------

12 JQ 0 14144

1 row selected.

四、简单例子

一个简单例子：网管网https://www.wendangku.net/doc/7c8104508.html,

创建测试表

SQL> create table TEST(a date);

表已创建。

创建一个自定义过程

SQL> create or replace procedure MYPROC as 2begin

3insert into TEST values(sysdate);

4end;

5/

过程已创建。

创建JOB

SQL> variable job1 number;

SQL>

SQL> begin

2

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

4/

PL/SQL 过程已成功完成。

运行JOB

SQL> begin

2dbms_job.run(:job1);

3end;

4/

PL/SQL 过程已成功完成。

SQL> select to_char(a,'yyyy/mm/dd hh24:mi:ss') 时间from TEST;

时间

-------------------

2001/01/07 23:51:21

2001/01/07 23:52:22

2001/01/07 23:53:24

删除JOB

SQL> begin

2dbms_job.remove(:job1);

3end;

4/

PL/SQL 过程已成功完成。

初始化相关参数job_queue_processes

alter system set job_queue_processes=39 scope=spfile;//最大值不能超过1000 ;job_queue_interval = 10 //调度作业刷新频率秒为单位

job_queue_process 表示oracle能够并发的job的数量，可以通过语句

show parameter job_queue_process;

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

ALTER SYSTEM SET job_queue_processes = 10;

来调整启动oracle的job。

相关视图：

dba_jobs

all_jobs

user_jobs

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

－－－－－－－－－－－－－－－－－－－－－－－－－

提交job语法：

begin

sys.dbms_job.submit(job => :job,

what => 'P_CLEAR_PACKBAL;',

next_date => to_date('04-08-2008 05:44:09', 'dd-mm-yyyy

hh24:mi:ss'),

interval => 'sysdate+ 1/360');

commit;

end;

/

－－－－－－－－－－－－－－－－－－－－－－－－－

创建JOB

variable jobno number;

begin

dbms_job.submit(:jobno,

'P_CRED_PLAN;',SYSDATE,'SYSDATE+1/2880',TRUE);

commit;

运行JOB

SQL> begin

dbms_job.run(:job1);

end;

/

删除JOB

SQL> begin

dbms_job.remove(:job1);

end;

/

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) 任务运行的其他一些会话参数

－－－－－－－－－－－－－－－－－－－－－－－－－－

描述INTERVAL参数值

每天午夜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）'

－－－－－－－－－－－－－－－－－－－－－－－－－－

1:每分钟执行

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

或

Interval => sysdate+1/1440

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

每天1点执行的oracle JOB样例

DECLARE

X NUMBER;

BEGIN

SYS.DBMS_JOB.SUBMIT

( job => X,

what => 'ETL_RUN_D_Date;',

next_date => to_date('2009-08-26 01:00:00','yyyy-mm-dd

hh24:mi:ss'),

interval => 'trunc(sysdate)+1+1/24',

no_parse => FALSE

);

SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x)); COMMIT;

END;

/

以上是明确指定每天的1点执行此job,如果指定是每天中午12点执行interval 需要指定为'trunc(sysdate)+1+12/24',如果仅仅指定interval为一天,这样当你手工用dbms_job.run(job)去运行一次时,job每天的执行时间是会改变的,如果你想job每天在固定时间执行,可以参考上面的例子.

初始化相关参数job_queue_processes

alter system set job_queue_processes=39 scope=spfile;//最大值不能超过1000 ;job_queue_interval = 10 //调度作业刷新频率秒为单位

job_queue_process 表示oracle能够并发的job的数量，可以通过语句show parameter job_queue_process;

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

ALTER SYSTEM SET job_queue_processes = 10;

来调整启动oracle的job。

相关视图：

dba_jobs

all_jobs

user_jobs

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

－－－－－－－－－－－－－－－－－－－－－－－－－

提交job语法：

begin

sys.dbms_job.submit(job => :job,

what => 'P_CLEAR_PACKBAL;',

next_date => to_date('04-08-2008 05:44:09', 'dd-mm-yyyy

hh24:mi:ss'),

interval => 'sysdate+ 1/360');

commit;

end;

/

－－－－－－－－－－－－－－－－－－－－－－－－－

创建JOB

variable jobno number;

begin

dbms_job.submit(:jobno,

'P_CRED_PLAN;',SYSDATE,'SYSDATE+1/2880',TRUE);

commit;

运行JOB

SQL> begin

dbms_job.run(:job1);

end;

/

删除JOB

SQL> begin

dbms_job.remove(:job1);

end;

/

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) 任务运行的其他一些会话参数

－－－－－－－－－－－－－－－－－－－－－－－－－－

描述INTERVAL参数值

每天午夜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）'

－－－－－－－－－－－－－－－－－－－－－－－－－－

1:每分钟执行

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

或

Interval => sysdate+1/1440

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不运行的检查步骤：

ORACLE有一种定时调度机制，用dbms_job包来管理。

设置的JOB就是不运行，搞得的郁闷，

最好执行了这个才搞定exec dbms_ijob.set_enabled(true);

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

1) Instance in RESTRICTED SESSIONS mode?

Check if the instance is in restricted sessions mode:

select instance_name,logins from v$instance;

If logins=RESTRICTED, then:

alter system disable restricted session;

^– Checked!

2) JOB_QUEUE_PROCESSES=0

Make sure that job_queue_processes is > 0

show parameter job_queue_processes

^– Checked!

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$ksppi

a,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?

Make sure a commit is issued after submitting the job:

BEGIN

SYS.DBMS_JOB.SUBMIT

(

job => X

,what => …dbms_utility.analyze_schema

(”SCOTT”,”COMPUTE”,NULL,NULL,NULL);?

,next_date => to_date(?08/06/2005 09:35:00′,?dd/mm/yyyy

hh24:mi:ss?)

,no_parse => FALSE

);

COMMIT;

END;

/

If the job executes fine if forced (i.e., exec

dbms_jobs.run();), then likely a commit

is missing.

^– Checked! The job is committed after submission.

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

Check dba_jobs_running to see if the job is still running:

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.

9) 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.

10) 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

11) 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:

1. Shutdown all applications, including databases.

2. Shutdown the server (machine)

3. Restart all applications, including databases.

4. Check that jobs are executing automatically.

from metalink docs ：313102.1

--查询存储过程是否正在执行

select count(*)

from SYS.V_$ACCESS a,SYS.V_$session b where a.type='PROCEDURE' and a.OBJECT like'MY_JOB_TEST'and a.sid=b.sid and b.status='ACTIVE';