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.

Sudhir
sudhir033198Asked:
Who is Participating?
 
catchmeifuwantConnect With a Mentor Commented:
Check the value of JOB_QUEUE_PROCESSES in the Init file.

Set it to some significant value and try again...
0
 
baonguyen1Commented:
Did you use commit when submitting ?
0
 
edvinkvCommented:
I think you havent submited the job properly. pls check your submission script.
thx.
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
baonguyen1Connect With a Mentor Commented:
For instance:

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


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

0
 
schwertnerConnect With a Mentor Commented:
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;
0
 
seazodiacConnect With a Mentor Commented:
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
0
 
schwertnerCommented:
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
_job_queue_interval  
0
 
seazodiacCommented:
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.
0
 
schwertnerCommented:
seazodiac:
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-  9.0.2.4 and 2  - 8.1.6
0
 
seazodiacCommented:

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

Sudhir:
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:

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


good luck!!!!
0
 
schwertnerCommented:
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!
0
 
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.