oracle job execute,Oracle job 执行问题

Oracle里的 job 执行出问题了，plsql

developer中看上次执行时间和下次执行时间都正常，也未标明为执行失败，也不是broken状态。但job

执行结果与预期不一致，就与job未执行的情况一样，而在外部手动执行job里的内容，又是成功的。情况是这样的，其中有一些job是需要从另一个数据库

复制数据的。搜索oracle 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;

2) JOB_QUEUE_PROCESSES=0

Make sure that job_queue_processes is > 0

show parameter job_queue_processes

alter system set job_queue_processes = 10 scope=spfile;

alter   system   set   job_queue_processes   =   10;

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

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.

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.

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

7) DBA_JOBS_RUNNING

Check dba_jobs_running to see if the job is still running:

select * from dba_jobs_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.

测试一下，发现不是上述问题。另外有个情况，当前数据库复制数据的源数据库是这样的情况，有2个用户，用户下面都有相同的数据源表，但表结构略有不同。于

是猜测，是否是数据库不知道具体去哪个表里取数据，而不在job里在外部环境执行时，会去找与当前用户名相同的用户下的表，正好有这样的情况，job则好

像是在dba下执行的。查看数据库连接(database link)，发现使用的数据库连接没有设置用户名和密码，添加进去后，问题竟然解决了。