Results 1 to 3 of 3

Thread: sequencial jobs

  1. #1
    Join Date
    May 2004
    Posts
    95

    Unanswered: sequencial jobs

    Hi.
    I've got a large number of materialized views, and they must be refreshed in a order, and the next one should only start when the last one completes sucessfully.

    What I'm doing now it's putting each refresh on a job, starting each one with several hours apart. But this is not accurate, because I realy never know how much time a refresh take.

    Could someone point me a better solution?

    Thanks.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    If they need to be refreshed in exact order, why don't you put all of them into a single script and run it (either at once, or schedule through a DBMS_JOB or DBMS_SCHEDULER)? Something like
    Code:
    CREATE OR REPLACE PROCEDURE prc_refresh_mv IS
    BEGIN
      dbms_mview.REFRESH('mv_employees');
      dbms_mview.REFRESH('mv_departments');
      dbms_mview.REFRESH('mv_jobs');
    END;
    Run it as
    Code:
    execute prc_refresh_mv;
    or schedule as
    Code:
         DECLARE
           jobno NUMBER;
         BEGIN
           dbms_job.submit(jobno, 
                           what      => 'prc_refresh_mv;',
                           next_date => TRUNC(SYSDATE) + 16/24,
                           interval  => 'SYSDATE + 1'
                 );
           commit;
         END;

  3. #3
    Join Date
    May 2004
    Posts
    95
    the simple things always passes me by.......
    many thanks.

Posting Permissions

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