ORACLE JOB不能按预期执行的常见原因


最近在一个测试环境中建立物化视图远程同步环境,但是远程视图一直未按照预期的时间进行刷新。以至于一开始让我怀疑ONDEMAND与STARTWITHNEXTBY冲突。但再三确认,发现是我的测试环境中ora_cjq0进程不存在,唉!开始怎么不往这方面去想呢,修改一下JOB_QUEUE_PROCESSES参数,一切恢复正常,突然发现ORACLE JOB方面的问题还不少,贴一篇METALINK文章,历数以前碰到的JOB失效的原因,无非以下几种:

 

  JobsNotExecuting Automatically文档ID:注释:313102.1
  Symptoms
  Jobs arenolonger executing automatically.
  Ifforced(execdbms_job.run();),theseexecutefine.
  Cause
  Tryingthemost common reasons why jobs don't execute automatically and as scheduled:

 

  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’;
  ^-- 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:
  DECLARE X NUMBER;
  BEGIN
  SYS.DBMS_JOB.SUBMIT
  (
  job => X
  ,what => 'dbms_utility.analyze_schema
  (''SCOTT'',''COMPUTE'',NULL,NULL,NULL);'
  ,next_date => to_date('08/06/200509: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 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.

 

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



相关阅读:
妙用脚本和批处理清除电脑中的痕迹
css textarea 高度自适应,无滚动条
Oracle Developer 2000中的一些实用语句
CSS教程之CSS盒模型
Memcached深度分析
编译Linux内核的详细过程
使用SQLDMO从SQL Server中获取信息
Windows7 显示桌面方法小结
社区(php&&mysql)三
SQL Server 2000的视图中必须小心使用*符号
基于jQuery替换table中的内容并显示进度条的代码
对联广告代码效果之二
另一个角度谈谈DIV CSS
LINUX中内部文件服务器搭建指南
快速导航

Copyright © 2016 phpStudy |