Could anyone explain why dbms_scheduler appears to have simply stopped processing jobs which have been running fine for a long time?
For example:
SQL > select start_date, last_start_date, repeat_interval,
run_count, enabled from user_scheduler_jobs
where job_name='REMOTE_DATA_TRF';
START_DATE LAST_START_DATE
---------------------------------------- ----------------------------------------
REPEAT_INTERVAL RUN_COUNT ENABL
------------------------ ---------- -----
14/DEC/07 12:37:38.499000 AM +00:00 04/MAY/08 05:37:38.109000 PM +00:00
FREQ=DAILY; BYHOUR=7,17 286 TRUE
That's a job that's enabled, been created since 14th December 2007, has run twice a day since then without a problem... and hasn't now run since 4th May. There are 15 other jobs exhibiting the same symptoms!
As far as I know, nothing has been altered on the database configuration side of things, nor on the server side of things (we're running 64-bit 10g on Windows 2003 64-bit).
I have tried doing this:
create procedure testproc
is
x date;
begin
select sysdate into x from dual;
end;
begin
dbms_scheduler.create_job(job_name => 'testjob',
job_type => 'STORED_PROCEDURE',
job_action => 'TESTPROC',
repeat_interval => 'freq=secondly; interval=10',
start_date=> sysdate;
enabled => true);
end;
...hoping to see a brand new job run quite frequently, but the 'LAST_START_DATE' column stays defiantly blank, even though I wait for 5 or 10 minutes.
I've also restarted the database and that hasn't altered the situation at all.
I'm at a loss: what can cause the database to stop processing scheduled jobs (apart from an end_date, which I never specify)? Is there some background process I should check to see is running whose absence would cause this problem?
I'd appreciate any suggestions or thoughts on this.