dbms_job

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?

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

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!

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.

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???

Do you have snp daemon processes running ?

Sorry, how can I tell?

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.

to see all background processes

select * from V$BGPROCESS;

regards

Dave.

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

did you issue commit after submit?

Yep. I've tried it with the commit in the block, out of the block, both in and out of the block. I've tried it every way that I can think of and can't ever get the thing to run on it's own.

jtanders,

There is a reported bug [BUG:328164] that is : "DBMS_JOB WILL NOT EXECUTE A PROCEDURE IF PUT IN THE JOB QUEUE
BY ANOTHER."

Pl see if you are being hit by this.....

does your job take more time than the job interval ??

does your job take more time than the job interval ??

No.

There is a reported bug [BUG:328164] that is : "DBMS_JOB WILL NOT EXECUTE A PROCEDURE IF PUT IN THE JOB QUEUE
BY ANOTHER."


That particular bug does not apply however, I found another one that may (Bug 1930074). It seems to be a problem with 'alter system disable restricted'. Apparently, if the instance was started in restricted mode then the statement “alter system disable restricted session” was issued, the problem arises. If you shutdown the database and startup normal the problem should go away. I haven’t had a chance to shutdown the database yet so I can’t confirm this but, I believe this is where the problem lies because the instance was brought up “restricted”.
I will post again if I find something different.