• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 891
  • Last Modified:

oracle dba_jobs interval after job completes

I would like to re schedule a job in DBA_JOBS to run 5 minutes after the last run completes. The job sometimes takes 3 seconds to run up to 4 hours to run.  How do i set the interval to be 5 minutes after completions. Or is there another method in DBA_JOBS to accomplish this.

Oracle 10.2

Yes I know that DBA_JOBS is going to the depreciated , but that is what I am required to use.

Thanks.
0
fpkeegan
Asked:
fpkeegan
  • 3
  • 2
1 Solution
 
slightwv (䄆 Netminder) Commented:
Have whatever the job executes resubmit itself in 5 minutes as it's last act.
0
 
paquicubaCommented:
Have whatever the job executes resubmit itself in 5 minutes as it's last act.

When is it going to stop?


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Go here --> http://docs.oracle.com/cd/B19306_01/server.102/b14231/scheduse.htm 
then click on "Using Chains" <-- a chain is what you need.

I don't have access to an Oracle database right now, so I cannot show you a working example. But if you cannot figure it out by Monday, I can help you with it.
0
 
slightwv (䄆 Netminder) Commented:
paquicuba,

Chains are DBMS_SCEHDULER.  They stated they have to use DBMS_JOB.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
paquicubaCommented:
slightwv, You're right, I didn't pay attention to that requirement. It looks like someone at company is about to retire and afraid to start using new features.
0
 
fpkeeganAuthor Commented:
In a major corporation it will cost many millions of $  to change a standard.  All purchased applications will need to be upgraded.  All in-house applications will need to be updated. Documentation updated.  Developers trained in the new standard. That is why  functions are deprecated but not removed.


Back to the problem… I tried to have the job resubmit itself, it does not work.  
  1)Scheduled the job with no interval specified, internal to the running SQL  set the next run time in the future by 5 minutes. The job was not in the DBA_JOBS table after the first run.  
        select job into v_jobno from sys.dba_jobs where upper(what) like '%TEST_JOB%';
       IF v_JOBNO != 0 THEN
               dbms_job.NEXT_DATE (v_jobno,sysdate+ (5/(24*60)) );
           commit;
      END IF;

2) Schedule the job with an INTERVAL of sysdate+1, one day in the future ran the test job . It remain in the DBA_JOBS table after the run, but the NEXT_Date was 1 day in the future not 5 minutes in the future.
0
 
slightwv (䄆 Netminder) Commented:
I wasn't suggesting you used dbms_job.next_date.  I was suggesting the procedure recreate/submit the entire job.  It would actually have the DBMS_JOB.SUBMIT code in it.

If you leave the interval parameter null it will only run once.
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.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now