How to Create a Scheduler Job in Oracle 12c

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

Enable / Disable a job

BEGIN 
  DBMS_SCHEDULER.ENABLE('myjob');
END;

BEGIN 
  DBMS_SCHEDULER.DISABLE('myjob');
END;

Monitoring jobs

SELECT * FROM dba_scheduler_jobs WHERE job_name = 'MY_JAVA_JOB';
SELECT * FROM dba_scheduler_job_log WHERE job_name = 'MY_JAVA_JOB';

or checking from JOB owner schema

 SELECT * FROM user_scheduler_jobs WHERE job_name = 'MY_JAVA_JOB';
 SELECT * FROM user_scheduler_job_log WHERE job_name = 'MY_JAVA_JOB';

Use user_scheduler_jobs and user_scheduler_job_log to only see jobs that belong to your user (current schema).

Share this post: on Twitter on Facebook on Google+