Solved

Job Scheduling in Oracle 8i

Posted on 2003-12-08
14
942 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
  • 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 64 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
 
LVL 8

Assisted Solution

by:baonguyen1
baonguyen1 earned 62 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 47

Assisted Solution

by:schwertner
schwertner earned 62 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 62 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 47

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 47

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 47

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to recover a database from a user managed backup

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now