Job Scheduling in Oracle 8i

Hi All,

I want to schedule execution of a procedure on Oracle 8i ( 8.1.5 ) server,
so that it runs automatically. I have submitted that procedure using DBMS_JOB.SUBMIT
function & has defined to the interval to run as Every Hour ( SYSDATE + 1/24 ).
My problem is that procedure is not getting executed If i manually run that job
using JOBID it runs fine.

So what can be the problem ?

Is there Anybody who can help me out ?

Thanks in advance.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Did you use commit when submitting ?
Check the value of JOB_QUEUE_PROCESSES in the Init file.

Set it to some significant value and try again...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
I think you havent submited the job properly. pls check your submission script.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

For instance:

SQL> variable jobno number;  
      DBMS_JOB.SUBMIT(:jobno, 'your_yob;' ,SYSDATE, SYSDATE + 1/24');  

And remeber to set the following parameters in init<SID>.ora

JOB_QUEUE_PROCESSES= n  # two background processes- if n=0 job will not run
JOB_QUEUE_INTERVAL=60  #the processes wake up every 60 seconds

In 8i you should grant to thje user a privilege:
SQL>grant analyze any to sys;
If the user is not SYS put there the user name.
The job schediler will not run the job if it is in the past.
So after creating the job with SYSDATE you have to plan the first running
in the next minutes (somewher in the FUTURE) using:

update dba_jobs SET next_date=TO_DATE('08-12-2003 14:55','DD-MM-YYYY HH24:MI') where job=1;

To analyze what happens with the jobs as SYS use:

select next_date, interval from dba_jobs;
YOU are MISSING one important parameter in the init<SID>.ora file.

JOB_QUEUE_PROCESSES = 4            #any values greater than 1 to make the jobs run asynch...

and you DON'T, ABSOLUTELY don't need to set JOB_QUEUE_INTERVAL value, otherwise, it will override the job interval value you set in the job submit command.

that's all you need to fix your problem
job_queue_interval exists in every oracle version.

job_queue_interval ABSOLUTELY has nothing to do with the INTERVAL VALUE of DBMS_JOB.SUBMIT procedure.

job_queue_interval  only points how often the background process CJQ0 -Coordinator Job Queue will check if it is time to launch the procedure.

I have checked this with 8i - it works.
In 9i job_queue_interval  is hidden:
1. by default it is 5 secs
2. if you want to override it you should use
My apology about comments on the job_queue_interval.

but Sudhir: you definitely need to set JOB_QUEUE_PROCESSES, that's for sure.

I think baonguyen1 has got the right answer for you.

Schwertner: thx for correcting me.
---->job_queue_interval ABSOLUTELY has nothing to do with the INTERVAL VALUE of DBMS_JOB.SUBMIT procedure.

as a matter of fact, there is some association between job_queue_interval and interval value of job schedules, and yet very critical somewhat...

if the interval values of job schedule is less than JOB_QUEUE_INTERVAL, the jobs never gets run on the interval values, it actaully on the job_queue_interval value. by the way, in oracle8i ,the default value for job_queue_interval is 60 seconds.
Do not be in hurry!

Sudhir; Yesterday I was in your position: the scheduled procedure did not start! Why????
Look at this:
DBMS_JOB.SUBMIT(:jobno, 'your_yob;' ,SYSDATE, SYSDATE + 1/24');

It seems that when the background process CJQ0 sees scheduled jobs (even seconds) in the past it will not run them.
So after scheduling the job move the start time some minutes in the future:

update dba_jobs SET next_date=TO_DATE('08-12-2003 14:55','DD-MM-YYYY HH24:MI') where job=1;

and the job will start normally and reschedeluded also normaly.

Yesterday I really succeded to solve this problem on 4 Oracle instances: 2- and 2  - 8.1.6

---->It seems that when the background process CJQ0 sees scheduled jobs (even seconds) in the past it will not run them.
---->So after scheduling the job move the start time some minutes in the future:

----->update dba_jobs SET next_date=TO_DATE('08-12-2003 14:55','DD-MM-YYYY HH24:MI') where job=1;

Schwertner: show me your job submission scripts, there must be something wrong in your script or your init<SID>.ora job parameters.

I have tested too both in Oracle8i and oracle9i, You DON'T Need to do that.

As long as you follow Baonguyen's solution, you will be fine.

So summarize:

1. define job_queue_processes parameter in your init<SID>.ora file, you can leave job_queue_interval to the default.
2. Second but more importantly, since people tend to omit this, COMMIT after your submit the job.
as an example:

jobnum number;
dbms_job.submit(jobnum, 'run_job;', sysdate, 'sysdate + 1/(24*60)');
commit;          ---COMMIT Is required absolutely.....

good luck!!!!
I have not issued commit.
May be this is the reason.
Because dbms_job.submit does nothing but  inserts a row in the table
which partly can be seen via the view dba_jobs.
Thanks to all of you!
sudhir033198Author Commented:
Thank U all for U'r valuable reply.

I will try out this
This will definitely solve my problem otherwise I will get back...

Thank U Very Much again.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.