Create and manage Schedule Jobs in Oracle with DBMS Scheduler

DBMS Scheduler is oracle inbuilt scheduler. It’s help to schedule the task which performed on specific timing and intervals. It help to automate the process needed for application and database level.

Check the job scheduled in oracle database:

set line 200 pages 200
col job_name for a30
col state for a10
SELECT job_name, state FROM dba_scheduler_jobs WHERE job_name='GATHER_STATS_JOB';


SELECT job_NAME, enabled, run_count, failure_count, job_action, repeat_interval FROM DBA_SCHEDULER_JOBS;


Checked the status of the DBMS Scheduler job:

set line 200 pages 200
col job_name for a30
col status for a10
SELECT log_id, job_name, status, to_char(log_date,'DD-MON-YYYY HH24:MI') log_date,run_duration FROM dba_scheduler_job_run_details WHERE job_name = 'GATHER_STATS_JOB';


Check all running DBMS Scheduler jobs

SELECT * FROM ALL_SCHEDULER_RUNNING_JOBS;


Check scheduler window name:

SELECT * FROM DBA_SCHEDULER_WINGROUP_MEMBERS;


Find the detail of window scheduler:

select WINDOW_NAME,RESOURCE_PLAN, START_DATE,REPEAT_INTERVAL,END_DATE,DURATION,ENABLED,ACTIVE from dba_scheduler_windows;


Example of scheduling PLSQL Block in DBMS Scheduler job:
For scheduling the job DBMS Scheduler is used create job procedure. In following example the job is running every minutes and executing the PLSQL Blocks written in PLSQL Language. so, you can set REPEAT_INTERVAL & JOB_TYPE parameter according to your need.

BEGIN
dbms_scheduler.create_job(
job_name => 'JOB_NAME'
,job_type => 'PLSQL_BLOCK'
,job_action => 'BEGIN pkg_workflow_mgmt.RUN_JOB (p_triggered_from => ''S'', p_triggered_on => ''10-Feb-2011''); END;'
,start_date => SYSDATE +1/24/59 -----
,repeat_interval => 'freq=MINUTELY;interval=1'
,enabled => TRUE
,comments => 'Demo for job schedule');
END;
/


Example of Scheduling Window Batch job:
In another example, we will execute the windows bat file in DBMS SCHEDULER. so, you need to set REPEAT_INTERVAL & JOB_TYPE parameter accordingly.

BEGIN
dbms_scheduler.create_job(
job_name => 'RMAN_JOB'
,job_type => 'EXECUTABLE'
,job_action => 'c:\windows\system32\cmd.exe /c D:\scripts\rman_full.bat > D:\scripts\k.txt'
,start_date => SYSDATE +1/24/59
,repeat_interval => 'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=00; BYMINUTE=25;'
,enabled => TRUE
,comments => 'RMAN BACKUP for job schedule');
END;
/


Parameters:
Job_name: Name of job
Job_type: PLSQL BLOCK , Procedure, function , EXECUTABLE
Job_action: syntax which you want to execute
start_date: from which date job will start
repeat_interval: Interval at which frequency it execute like daily, every hour etc
Comments: Info about job

Example of repeat interval

'FREQ=DAILY; BYDAY=SUN; BYHOUR=18;' ----on sunday at 18:00
'freq=MINUTELY;interval=1' --Every 1 minute
'freq=MINUTELY;interval=5' --Every 5 minute
'freq=HOURLY;interval=1' --Every hour
'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI,SAT,SUN; BYHOUR=22;' --Daily at 22:00
'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI,SAT,SUN; BYHOUR=22; BYMINUTE=30' -- Daily at 22:30


Check the job status is enable or disable:

SELECT job_NAME, enabled, run_count, failure_count, job_action FROM DBA_SCHEDULER_JOBS WHERE job_name LIKE 'GATHER_STATS_JOB';


Enable the DBMS Scheduler job:

EXEC dbms_scheduler.ENABLE('Job_Name');


Disable the DBMS Scheduler job

EXEC dbms_scheduler.DISABLE('Job_Name');


For run the job manually:

exec dbms_scheduler.run_job('job_name');


For drop the job:

EXEC dbms_scheduler.drop_job(job_name => 'job_name');


Verified the job is Dropped:

SELECT job_NAME, enabled, run_count, failure_count, job_action FROM DBA_SCHEDULER_JOBS WHERE job_name LIKE 'JOB_NAME';


Example to scheduled the jobs with at different time intervals:

--Run at 10:00 pm daily from Monday to Friday:
FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=22; BYMINUTE=0; BYSECOND=0;
--Run every hour:
FREQ=HOURLY;INTERVAL=1;
--Run every 5 minutes:
FREQ=MINUTELY;INTERVAL=5;
--Run every Friday at 9:00 am (All three examples are equivalent):
FREQ=DAILY; BYDAY=FRI; BYHOUR=9; BYMINUTE=0; BYSECOND=0;
FREQ=WEEKLY; BYDAY=FRI; BYHOUR=9; BYMINUTE=0; BYSECOND=0;
FREQ=YEARLY; BYDAY=FRI; BYHOUR=9; BYMINUTE=0; BYSECOND=0;
--Run every other Friday:
FREQ=WEEKLY; INTERVAL=2; BYDAY=FRI;
--Run on Monday of weeks 5, 10 and 15 every year:
FREQ=YEARLY; BYWEEKNO=5,10,15; BYDAY=MON
--Run on the last day of every month.
FREQ=MONTHLY; BYMONTHDAY=-1;
--Run on the next to last day of every month:
FREQ=MONTHLY; BYMONTHDAY=-2;
--Run on March 10th (Both examples are equivalent):
FREQ=YEARLY; BYMONTH=MAR; BYMONTHDAY=10;
FREQ=YEARLY; BYDATE=0310;
--Run every January 10, 11, 12, 13 and 14 (Both examples are equivalent):
FREQ=YEARLY; BYDATE=0110,0111,0112,0113,0114
FREQ=YEARLY; BYDATE=0110+SPAN:5D;
--Run every 10 days:
FREQ=DAILY; INTERVAL=10;
--Run daily at 4:15, 5:15, and 6:15PM:
FREQ=DAILY; BYHOUR=16,17,18; BYMINUTE=15; BYSECOND=0;
--Run on the 15th day of every other month:
FREQ=MONTHLY; INTERVAL=2; BYMONTHDAY=15;
--Run on the 29th day of every month:
FREQ=MONTHLY; BYMONTHDAY=29;
--Run on the second Wednesday of each month:
FREQ=MONTHLY; BYDAY=2WED;
--Run on the last Friday of the year:
FREQ=YEARLY; BYDAY=-1FRI;
--Run every 50 hours:
FREQ=HOURLY; INTERVAL=50;
--Run on the last day of every other month:
FREQ=MONTHLY; INTERVAL=2; BYMONTHDAY=-1;
--Run hourly for the first three days of every month:
FREQ=HOURLY; BYMONTHDAY=1,2,3;
--Run on the 60th, 120th and 180th days of the year:
FREQ=YEARLY; BYYEARDAY=60,120,180;
--Run on the last workday of every month (assuming that workdays are Monday through Friday):
FREQ=MONTHLY; BYDAY=MON,TUE,WED,THU,FRI; BYSETPOS=-1