Oracle DBMS_SCHEDULER Jobs

Oracle 10g has extended the scheduler functionality for DBMS_JOB package by introducing DBMS_SCHEDULER jobs. Since it is a server-based architecture, hence every process will run on server instead of running on client machine. Oracle DBMS_SCHEDULER package helps in scheduling procedures and functions.
Oracle DBMS_SCHEDULER Jobs
The following example will help you in creating jobs. It has been divided into following parts:

-- To run every 2 minute, every day
BEGIN
   DBMS_SCHEDULER.create_schedule (
      schedule_name     => 'INTERVAL_EVERY_2_MINUTES',
      start_date        => SYSTIMESTAMP,
      repeat_interval   => 'freq=MINUTELY;interval=2',
      comments          => 'Runtime: Every day all 2 minutes'
   );
END;

-- To call a procedure of a database package
BEGIN
   DBMS_SCHEDULER.create_program (
      program_name     => 'PROG_INSERT_TEST_DATA',
      program_type     => 'STORED_PROCEDURE',
      program_action   => 'SP_TEST',
      enabled          => TRUE,
      comments         => 'Procedure to insert test data'
   );
END;

-- To connect both dbms_scheduler parts by creating the final job
BEGIN
   DBMS_SCHEDULER.create_job (job_name        => 'JOB_INSERT_TEST_DATA',
                              program_name    => 'PROG_INSERT_TEST_DATA',
                              schedule_name   => 'INTERVAL_EVERY_2_MINUTES',
                              enabled         => TRUE,
                              auto_drop       => FALSE,
                              comments        => 'Job to insert test data');
END;


-- To view all jobs
SELECT   * FROM user_scheduler_jobs;

-- To get information of job
SELECT   *
 FROM   user_scheduler_job_log
ORDER BY   log_date DESC;

-- To show details on job run
SELECT * FROM user_scheduler_job_run_details;

-- To Restart Job
BEGIN
   DBMS_SCHEDULER.disable ('JOB_INSERT_TEST_DATA');
   DBMS_SCHEDULER.enable ('JOB_INSERT_TEST_DATA');
END;

-- To run job immediate
BEGIN
   DBMS_SCHEDULER.run_job ('JOB_COLLECT_SESS_DATA', TRUE);
END;

To change dbms_scheduler settings 
-- To change start time
DBMS_SCHEDULER.SET_ATTRIBUTE(name => 'INTERVAL_EVERY_2_MINUTES',attribute => 'start_date', 
value => to_date('22.06.2012 12:15','dd.mm.yyyy hh24:mi'));

-- TO change repeat interval
BEGIN
   DBMS_SCHEDULER.SET_ATTRIBUTE (name        => 'INTERVAL_EVERY_2_MINUTES',
                                 attribute   => 'repeat_interval',
                                 VALUE       => 'freq=MINUTELY;interval=4');
END;
Summary
Author Rating
1star1star1star1star1star
Aggregate Rating
no rating based on 0 votes
Software Name
Oracle DBMS_SCHEDULER Jobs
Operating System
Android
Price
INR Oracle DBMS_SCHEDULER Jobs

You may also like...

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.