Scheduling using DBMS_JOB

[THoey]

I have created a stored procedure that I run that refreshes some tables. I run it and it works successfully. I have scheduled the job to run every 15 minutes using the following SQL:

VARIABLE jobno number;
BEGIN
   DBMS_JOB.SUBMIT(:jobno, 
                   'ehgriff.refresh_static_tables;', 
                   SYSDATE,
                   'SYSDATE + 1/96');
   commit;
END;

I can query the USER_JOBS table and I see the job is scheduled and not broken. I then force the job to run the first time with the following:

exec dbms_job.run(14);

The procedure completes successfully and the next run time is incremented. But, if I wait until that time has come and gone, the job does not run as scheduled. Any ideas what I am doing wrong? Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[cheskey1977]

Terry,

I am fairly new to oracle DBA tasks, so I don't know if this document will help you, but it helped me understand dbms scheduler, and it may be able to troubleshoot your issue. It is from the Oracle User's Co-Operative FAQ.

LINK:

http://www.jlcomp.demon.co.uk/faq/dbms_job.html

Hope it helps!

Matt

 

[THoey]

Thanks, I'll take a look... Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[THoey]

I think that document pointed out the problem. I looked at the initialization parameters for the DB and the JOB_QUEUE_PROCESSES is set to zero. To me, this tells me that Oracle is not setting aside a process to check if there are jobs scheduled. I have requested the DBA of that instance to alter the parameter. We'll see if that was it.

Thanks for the link. I have saved it off and will look at some of the other info... Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...