{"id":126,"date":"2015-11-11T16:09:42","date_gmt":"2015-11-11T16:09:42","guid":{"rendered":"https:\/\/dbadave.co.uk\/?p=126"},"modified":"2016-02-09T21:51:24","modified_gmt":"2016-02-09T21:51:24","slug":"the-oracle-job-scheduler","status":"publish","type":"post","link":"https:\/\/dbadave.co.uk\/the-oracle-job-scheduler\/","title":{"rendered":"The Oracle Job Scheduler"},"content":{"rendered":"

Despite the fact that the Oracle job scheduler was introduced back in the 10g version of the database, it seems a lot of people are still using the old job scheduling mechanism – DBMS_JOBS. This is despite the many advantages the job scheduler has in terms of both functionality and ease of use. This article is going to take a look at the Oracle job scheduler, how it works, and why you should be using it rather than DBMS_JOBS.<\/p>\n

The Oracle Job Scheduler<\/strong><\/em><\/h2>\n

Configuring scheduled jobs under the Oracle job scheduler is effectively broken down into three distinct steps:<\/p>\n

    \n
  1. Firstly, create a schedule which determines when a job should run.<\/li>\n
  2. Then create a program. A program stores information about a task such as the name of the package or procedure to run, whether the task is enabled, and so on. It does not include schedule information.<\/li>\n
  3. Finally create the job – this is basically the link between the schedule and the program.<\/li>\n<\/ol>\n

    Creating A Job Schedule<\/strong><\/em><\/h3>\n

    A schedule is created by calling the DBMS_SCHEDULER.CREATE_SCHEDULE procedure. This procedure takes five parameters:<\/p>\n

      \n
    1. The schedule name.<\/li>\n
    2. The start date.<\/li>\n
    3. The repeat interval.<\/li>\n
    4. The end date.<\/li>\n
    5. A comment parameter.<\/li>\n<\/ol>\n

      An example of creating a job schedule is as follows:<\/p>\n

      BEGIN\r\n  DBMS_SCHEDULER.create_schedule (\r\n    schedule_name => 'my_schedule',\r\n    start_date => SYSTIMESTAMP,\r\n    repeat_interval => 'FREQ=DAILY', \r\n    end_date => NULL, \r\n    comments => 'Runs every day for ever.'); \r\nEND; \r\n\/<\/pre>\n
      The REPEAT_INTERVAL parameter offers many different ways of defining the interval and makes creating complex job schedules much easier than when using DBMS_JOB. You can find a full explanation of all the possible values for this parameter here<\/a>.<\/p>\n
      Creating A Program<\/strong><\/em><\/h3>\n
      A program is created by calling the DBMS_SCHEDULER.CREATE _PROGRAM procedure. This procedure takes a minimum of three, and up to six paramters:<\/p>\n
        \n
      1. The program name. Required.<\/li>\n
      2. The program action. Required.<\/li>\n
      3. The program type. Required.<\/li>\n
      4. Number of arguments. Defaults to 0.<\/li>\n
      5. Whether the program is enabled or not. Defaults to NO.<\/li>\n
      6. A comment parameter. Defaults to null.<\/li>\n<\/ol>\n
        An example of creating a program is as follows:<\/p>\n
        BEGIN\r\nDBMS_SCHEDULER.CREATE_PROGRAM (\r\n   program_name => 'my_program',\r\n   program_type => 'EXECUTABLE',\r\n   program_action => '\/usr\/local\/bin\/date',\r\n   enabled => 'YES',\r\n   comments => 'Runs the O\/S date command.');\r\nEND;\r\n\/<\/pre>\n
        The PROGRAM_TYPE parameter can have three different values:<\/p>\n
          \n
        1. PL\/SQL BLOCK<\/li>\n
        2. STORED_PROCEDURE<\/li>\n
        3. EXECUTABLE<\/li>\n<\/ol>\n
          If the PROGRAM_TYPE is set to EXECUTABLE, the PROGRAM_ACTION specifies a program that is external from the database. This effectively means an operating system command. You can find a full explanation of what all these parameters mean in the Oracle documentation.<\/p>\n
          Creating A Job<\/strong><\/em><\/h3>\n
          A job is created by calling the DBMS_SCHEDULER.CREATE_JOB procedure. This procedure can take a lot of parameters but the parameters needed\u00a0when you’ve already defined a schedule and a program as above, are as follows:<\/p>\n
            \n
          1. The job name. Required.<\/li>\n
          2. The program name. Required. This is the name of the program you created using the DBMS_SCHEDULER.CREATE_PROGRAM command.<\/li>\n
          3. The schedule name. Required. This is the name of the schedule you created using the DBMS_SCHEDULER.CREATE_SCHEDULE command.<\/li>\n
          4. Whether the job is enabled or not. Defaults to No.<\/li>\n
          5. A comment parameter. Defaults to null.<\/li>\n<\/ol>\n
            An example of creating a job using the schedule and program examples from above is as follows:<\/p>\n
            DBMS_SCHEDULER.CREATE_JOB (<\/p>\n
                job_name => 'my_job',\r\n    program_name => 'my_program',\r\n    schedule_name => 'my_schedule',\r\n    enabled => TRUE,\r\n    comments => 'Job defined by an existing program and schedule.');\r\n<\/pre>\n
            Again, you can find a full explanation of all the possible parameters for the CREATE_JOB procedure in the Oracle documentation.<\/p>\n
            Advantages Of DBMS_SCHEDULER\u00a0vs DBMS_JOB<\/strong><\/em><\/h2>\n
            The Oracle job scheduler has a number of features that DBMS_JOB doesn’t. For example:<\/p>\n