dbms_job

[jtanders]

I have a job that does not run automatically. It runs fine when I execute dbms_job.run(); but it never runs on the "next_date". After executing with dbms_job.run the next_date looks correct but it never runs.

The init.ora file has the following settings:
job_queue_processes = 2
job_queue_interval = 10

Here is the job:
BEGIN
dbms_job.submit(
:jobno,
'owner.proc();',
sysdate,
'SYSDATE + 10/1440'
);
commit;
END;
/

Any ideas?

[julian]

Delete it with dbms_job.remove() and run in SQL*Plus:

variable jobno number;
begin
dbms_job.submit(:jobno,'owner.proc();',trunc(sysdate) + 10/1440,'trunc(sysdate+1) + 10/1440');
commit work;
end;
/

[jtanders]

Thanks for the info but that didn't seem to work either. What it did was schedule the next_date for today at 12:10 with an interval of (sysdate + 1) + 10/1440.

What I want is for it to run immediately with an interval of 10 minutes. No matter what I try, it does not run immediately or even run on the next_date.

Thanks!

[dave777]

I created job as follows:

VARIABLE JOBNO NUMBER;
BEGIN
DBMS_JOB.SUBMIT(:JOBNO,'BEGIN UPDATE JOB_TEST SET RUNCOUNT = RUNCOUNT+1; COMMIT; END;',
SYSDATE,'SYSDATE + 2/1440');
END;
/

commit;

/I assume you ran commit after you created job/

this job increments runcount in job_test table. it executed immediately after I created it and ran every 2 min until I removed it.

by the way, job_queue_interval = 10 is a 10 sec interval for SNPn background process to wake up and check for jobs to run.
So, your init.ora parms look OK.

[jtanders]

I'm baffled! Dave, I did exactly what you did (except I modified the update statement for my use) and it is still not executing. I can run the job manually (dbms_job.run) but it will not run by itself.

Any other ideas???

[svk]

Do you have snp daemon processes running ?

[jtanders]

Sorry, how can I tell?

[svk]

Whats you "job_queue_processes" init parameter value ?

And snp processes are just like other database daemons like PMON,SMON. So, you should be able to see if ora_snp is running.

If not (job_queue_processes is set to zero), change the value to at least 1. You don't need to bounce the database to do this. You can say "alter system set job_queue_processes=1" and the daemon will start.

[dave777]

to see all background processes

select * from V$BGPROCESS;

regards

Dave.

[jtanders]

There are two snp processes running which makes sense because the init.ora file has the following parameters:

job_queue_processes = 2
job_queue_interval = 10