Here’s the Oracle Scheduler Job Example

You can create and schedule a job in Oracle by using certain conditions. The conditions can be when to run and procedure details. The frequency of the job you can set. Below is an example that you can use for your practice.

Note: The job scheduling you can do in SQL developer. Here are the steps.

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

References

• Oracle Job Scheduling
• Oracle PL/SQL for dummies