HOW TO CREATE A SCHEDULER JOB IN ORACLE 12C
RELATED BOOK
Oracle 12c For Dummies
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:
Log in to SQL*Plus as the SYS user.
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.
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.
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.
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.
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.
Enable the program:
<exec dbms_scheduler.enable('PROG_INSERT_USER_COUNT')>
You see this:
PL/SQL procedure successfully completed.
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.
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.
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.
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
