Here’s the Oracle Scheduler Job Example

Photo by Christopher Gower on Unsplash

1. Granting access to create a job

Here hr is the database instance.

grant create job to hr;

2. Let HR see the V$SESSION table

grant select on v$session to hr;

3. Crate a Table to hold user data

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

4. Create a stored procedure to hold data

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;
/

5. 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;
/

6. Enable the program

BEGIN 
dbms_scheduler.enable(‘PROG_INSERT_USER_COUNT’)
END;

7. Create a Scheduler

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;
/

8. Create a job with a schedule and program

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

9. Enable the job

BEGIN 
dbms_scheduler.enable(‘my_user_count_job’)
END;
/

10. Verify job execution

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

11. Disable the job

BEGIN
dbms_scheduler.disable(‘my_user_count_job’)
END;
/

12. Drop the job

BEGIN
dbms_scheduler.drop_job(‘my_user_count_job’)
END;

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store