oracle dba_jobs interval after job completes

Posted on 2012-08-17
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
    LVL 76

    Expert Comment

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

    Expert Comment

    Have whatever the job executes resubmit itself in 5 minutes as it's last act.

    When is it going to stop?

    Go here -->
    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 76

    Expert Comment

    by:slightwv (䄆 Netminder)

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

    Expert Comment

    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

    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 76

    Accepted Solution

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
    Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
    Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

    737 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

    22 Experts available now in Live!

    Get 1:1 Help Now