oracle dba_jobs interval after job completes

Posted on 2012-08-17
Medium Priority
Last Modified: 2012-09-09
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.

Question by:fpkeegan
  • 3
  • 2
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38306663
Have whatever the job executes resubmit itself in 5 minutes as it's last act.
LVL 23

Expert Comment

ID: 38308171
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.
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38311333

Chains are DBMS_SCEHDULER.  They stated they have to use DBMS_JOB.

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

LVL 23

Expert Comment

ID: 38314675
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.

Author Comment

ID: 38316705
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)) );
      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.
LVL 78

Accepted Solution

slightwv (䄆 Netminder) earned 1500 total points
ID: 38317084
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.

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses
Course of the Month16 days, 21 hours left to enroll

864 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