2 Replies Latest reply: Dec 29, 2012 5:28 AM by Mihael RSS

    How to do dbms scheduling?

    998899-Oracle
    998899-Oracle Sep 20, 2012 5:04 AM
      I have a set of sql scripts which has to be run on a periodical basis say every two hours.

      These scripts create tables which are populated from a remote database using db link and if and only if the process is completed without any problem, we truncate a set of different tables and populate with the data in the newly created tables and drop the newly created tables.

      I want it to be part dbms scheduling but i have no idea on how to do this.

      If someone could help would be great .

      Thanks in advance.
        • 1. Re: How to do dbms scheduling?
          user503635
          user503635 Dec 28, 2012 6:54 AM (in response to 998899-Oracle)
          You may try to write a stored procedure instead of a script. In the procedure, do the following
          1. Write a dynamic SQL 'Create temp table as select * from remote_table@remote_link' and then execute it.
          2. Write a dynamic SQL 'Truncate local_table' and execute it.
          3. Write a dynamic SQL 'INSERT INTO local_table select * from temp_table' and execute it.
          4. Commit


          Then create a scheduler job using DBMS_SCHEDUER.create_job to run the stored procedure at your preferred interval.

          DECLARE
          BEGIN
          dbms_scheduler.create_job(
          job_name=>'my_proc',
          job_type => 'stored_procedure',
          job_action=> 'my_procedure',
          repeat_interval => 'FREQ=HOURLY; INTERVAL=2',
          enabled => true,
          auto_drop=> false
          );
          END;
          • 2. Re: How to do dbms scheduling?
            Mihael
            Mihael Dec 29, 2012 5:28 AM (in response to 998899-Oracle)
            To simplify and optimize the procedure, you can create materialized views and then just refresh them when you need.