开发者社区> teacheryang> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

oracle job使用详解及job不运行的检查方法

简介: 每天1点执行的oracle JOB样例 DECLAREX NUMBER;BEGINSYS.DBMS_JOB.SUBMIT( job => X,what => 'ETL_RUN_D_Date;',next_date => to_date('2009-08-26 01:00:00','yyyy-m...
+关注继续查看

每天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=<job_number>;

  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(<job_no>);), 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=<job_number>;

^ 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=<job_number>;

  ^ 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 ;

  –<Wait for some time to ensure CJQ process stopped>

  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

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
Oracle 数据库性能优化3日实战(企业培训)
课程名称一: Oracle性能优化及调整 课程时长 1天 课程深度: 高级 上机实验: 10%-30% 授课对象: Oracle开发人员、Oracle数据库管理人员,应用程序开发人员 课程描述: 本课程讲述Oracle数据库物理层规划,系统性能的监控,数据库性能参数调整,统计信息的收集,使用自动化调试工具优化数据库,I/O子系统的配置与设计以及性能优化方法论等。
1129 0
+关注
teacheryang
某不知名高校教师
文章
问答
文章排行榜
最热
最新
相关电子书
更多
Azure Container Services --The best place to run your workload
立即下载
ORACLE 10g 数据库体系结构图
立即下载
Oracle 至PostgreSQL案例分享
立即下载