Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Posts
    33

    Unanswered: How to execute a job at a specified interval

    Hi

    I have to execute a PL/SQL procedure every day at 5.00AM in the morning. Could any one let me know the feasible solution.

    And let me know what happens when the instance is shutdown. Will the script be re-executed automatically when the server is restarted or again we need to submit the job?

  2. #2
    Join Date
    Sep 2003
    Location
    Assen, Nederland
    Posts
    55
    use dbms_job
    Hope that helped...
    Visit My Website : http://www.oraflame.com
    _____________________________
    Tarry Singh

    OCP DBA 8i
    Currently: SQL Server DBA 7,2000
    Oracle, PHP Programmer

  3. #3
    Join Date
    Sep 2003
    Posts
    33
    Thanks Tarry,

    Could you pls give the script for this job.

  4. #4
    Join Date
    Sep 2003
    Location
    Assen, Nederland
    Posts
    55
    here's you job...


    Code:
    DECLARE
       jobno   NUMBER;
    BEGIN
       DBMS_JOB.SUBMIT
          (job  => jobno
          ,what => 'your_proc; end;'
          ,next_date => SYSDATE
          ,interval  => 'SYSDATE+5/24');
       COMMIT;
    END;
    /
    and yes indeed there are always issues with the broken jobs...especially complex jobs, failure due to recurring executions, replication, recreated/modified tables, views...

    a repair_dbms_job proc may help here...

    Code:
    CREATE OR REPLACE PROCEDURE repair_dbms_jobs
    AS
            
       CURSOR repair_dbms_jobs_cur
       IS
       SELECT job
         FROM user_jobs
        WHERE broken = 'Y';
        
    BEGIN
       FOR job_rec IN repair_dbms_jobs_cur
       LOOP
          DBMS_JOB.BROKEN(job_rec.job,FALSE);
       END LOOP;
    END [repair_dbms_jobs];
    Hope that helped...
    Visit My Website : http://www.oraflame.com
    _____________________________
    Tarry Singh

    OCP DBA 8i
    Currently: SQL Server DBA 7,2000
    Oracle, PHP Programmer

  5. #5
    Join Date
    Mar 2002
    Location
    Ireland
    Posts
    181
    Hi,

    Tarry I think it should read:

    ,next_date => trunc(SYSDATE)+5/24
    ,interval => 'SYSDATE+1');

    If he wants it to run at 5:00AM every morning.

    Rgs,
    Breen.

  6. #6
    Join Date
    Sep 2003
    Location
    Assen, Nederland
    Posts
    55
    Breen,

    Indeed! thanx for the corection.
    Hope that helped...
    Visit My Website : http://www.oraflame.com
    _____________________________
    Tarry Singh

    OCP DBA 8i
    Currently: SQL Server DBA 7,2000
    Oracle, PHP Programmer

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •