Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Job Scheduling in Oracle 8i

Posted on 2003-12-08
14
Medium Priority
?
963 Views
Last Modified: 2007-12-19
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
0
Comment
Question by:sudhir033198
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +3
14 Comments
 
LVL 8

Expert Comment

by:baonguyen1
ID: 9902172
Did you use commit when submitting ?
0
 
LVL 12

Accepted Solution

by:
catchmeifuwant earned 256 total points
ID: 9902205
Check the value of JOB_QUEUE_PROCESSES in the Init file.

Set it to some significant value and try again...
0
 
LVL 1

Expert Comment

by:edvinkv
ID: 9902212
I think you havent submited the job properly. pls check your submission script.
thx.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 8

Assisted Solution

by:baonguyen1
baonguyen1 earned 248 total points
ID: 9902215
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
 
LVL 48

Assisted Solution

by:schwertner
schwertner earned 248 total points
ID: 9902441
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
 
LVL 23

Assisted Solution

by:seazodiac
seazodiac earned 248 total points
ID: 9903819
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
 
LVL 48

Expert Comment

by:schwertner
ID: 9903875
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
 
LVL 23

Expert Comment

by:seazodiac
ID: 9903982
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
 
LVL 48

Expert Comment

by:schwertner
ID: 9904095
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
 
LVL 23

Expert Comment

by:seazodiac
ID: 9904854

---->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
 
LVL 48

Expert Comment

by:schwertner
ID: 9910316
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
 

Author Comment

by:sudhir033198
ID: 9910873
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious sideā€¦
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question