This example shows you how to create a job in Oracle to run every hour using the DBMS_SCHEDULER
package.
1. Create a Job Using DBMS_SCHEDULER.CREATE_JOB
The following PL/SQL code will schedule a job that will start on 20th October 2019 at 1:00 AM India time to run every hour on every day. You can remove the Day Name from the repeat interval parameter to exclude a particular day. The job type is stored procedure and will run the procedure specified in the Job action parameter.
BEGIN DBMS_SCHEDULER.CREATE_JOB ( job_name => 'HOURLY_JOB', job_type => 'STORED_PROCEDURE', job_action => 'YOUR_SCHEMA.YOUR_PROCEDURE', start_date => '20-OCT-19 01.00.00 AM Asia/Kolkata', repeat_interval => 'FREQ=HOURLY;BYDAY=MON,TUE,WED,THU,FRI,SAT,SUN', end_date => NULL, auto_drop => FALSE, job_class => 'SYS.DEFAULT_JOB_CLASS', comments => 'run job every hour'); END; /
2. Enable the Job
BEGIN DBMS_SCHEDULER.enable('HOURLY_JOB'); END; /
Your hourly DBMS_SCHEDULER
job is created and enabled now. You can change the time zone from "Asia/Kolkata" to any country time zone by getting the value from the following query:
SELECT DISTINCT tzname, TZ_OFFSET (tzname) FROM V$TIMEZONE_NAMES ORDER BY tzname;