How to Create a Scheduler Job in Oracle 12c - dummies

How to Create a Scheduler Job in Oracle 12c

By Chris Ruel, Michael Wessler

The Oracle 12c Scheduler example here creates a simple job that runs a stored Programming Language/Structured Query Language (PL/SQL) procedure. The procedure selects a count of the number of users on the system and inserts that number into a table with a timestamp. It runs every five minutes.

Follow these steps to schedule a job for the first time:

  1. Log in to SQL*Plus as the SYS user.

  2. Give the intended job creator the ability to create jobs:

    <grant create job to hr;>

    You should see this:

    Grant succeeded.

    This example job is created and run by HR.

  3. Let HR see the V$SESSION table:

    <grant select on v_$session to hr;>

    You should see this:

    Grant succeeded.

    The _ in V_$SESSION isn’t a typo! V$SESSION is a synonym for V_$SESSION. For the grant to work, you have to give the view name.

  4. Log in to SQL*Plus as the job creator and make a table to hold the data:

    < create table user_count (
    number_of_users NUMBER(4),
    time_of_day   TIMESTAMP
    )
    TABLESPACE users;>

    You see this:

    Table created.
  5. Create a stored procedure:

    < CREATE OR REPLACE PROCEDURE insert_user_count AS
      v_user_count NUMBER(4);
    BEGIN
     SELECT count(*)
          INTO v_user_count
       FROM v$session
       WHERE username IS NOT NULL;
      INSERT INTO user_count
       VALUES (v_user_count, systimestamp);
      commit;
    END insert_user_count;
    / >

    The stored procedure gathers the number of users and inserts them into the table with a timestamp. You should see this:

    Procedure created.
  6. Create a program for the job:

    < BEGIN
    DBMS_SCHEDULER.CREATE_PROGRAM (
      program_name      => 'PROG_INSERT_USER_COUNT',
    program_action     => 'INSERT_USER_COUNT',
    program_type      => 'STORED_PROCEDURE');
    END;
    />

    You see this:

    PL/SQL procedure successfully completed.
  7. Enable the program:

    <exec dbms_scheduler.enable('PROG_INSERT_USER_COUNT')>

    You see this:

    PL/SQL procedure successfully completed.
  8. Create a schedule for the job to run:

    < BEGIN
    DBMS_SCHEDULER.CREATE_SCHEDULE (
     schedule_name   => 'my_weekend_5min_schedule',
     start_date    => SYSTIMESTAMP,
     repeat_interval  => 'FREQ=MINUTELY; INTERVAL=5; BYDAY=SAT,SUN',
     end_date     => SYSTIMESTAMP + INTERVAL '30' day,
     comments     => 'Every 5 minutes');
    END;
    />

    This example job runs every five minutes. You see this:

    PL/SQL procedure successfully completed.
  9. Create your job with the program and schedule you defined:

    < BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
      job_name     => 'my_user_count_job',
      program_name   => 'prog_insert_user_count',
      schedule_name   => 'my_weekend_5min_schedule');
    END;
    />

    You see this:

    PL/SQL procedure successfully completed.
  10. Enable your job so it runs within the defined schedule:

    < exec dbms_scheduler.enable('my_user_count_job’)>

    You see this:

    PL/SQL procedure successfully completed.

    The job runs at the specified start time (at SYSTIMESTAMP). If you choose a calendar date in the future, it doesn’t start until then.

  11. After the job’s been running for 17 minutes, type the following to see your USER_COUNT table:

    < select *
    from user_count;>

    You see this:

    NUMBER_OF_USERS TIME_OF_DAY
    --------------- ---------------------------------
           14 09-AUG-13 02.15.14.118495 PM
           14 09-AUG-13 02.00.14.137300 PM
           13 09-AUG-13 02.05.14.120116 PM
           13 09-AUG-13 02.10.14.120680 PM

When you have the job running, you can get details about the success or failure by querying the following views:

USER_SCHEDULER_JOB_RUN_DETAILS
USER_SCHEDULER_JOB_LOG

These views show information only about your jobs. To get information on the recent runs of our job, log in as the job creator and type

< select job_name, status, run_duration, cpu_used
from USER_SCHEDULER_JOB_RUN_DETAILS
where job_name = ‘MY_USER_COUNT_JOB’;>

You see this:

JOB_NAME       STATUS   RUN_DURATION  CPU_USED
-------------------- ---------- --------------- ------------------
MY_USER_COUNT_JOB  SUCCEEDED +000 00:00:00  +000 00:00:00.01
MY_USER_COUNT_JOB  SUCCEEDED +000 00:00:00  +000 00:00:00.01
MY_USER_COUNT_JOB  SUCCEEDED +000 00:00:00  +000 00:00:00.00
MY_USER_COUNT_JOB  SUCCEEDED +000 00:00:00  +000 00:00:00.01