Oracle Job Schedules with samples

Creates a job in a single call without using an existing program or schedule:

DBMS_SCHEDULER.CREATE_JOB (
job_name IN VARCHAR2,
job_type IN VARCHAR2,
job_action IN VARCHAR2,
number_of_arguments IN PLS_INTEGER DEFAULT 0,
start_date IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
repeat_interval IN VARCHAR2 DEFAULT NULL,
end_date IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
job_class IN VARCHAR2 DEFAULT 'DEFAULT_JOB_CLASS',
enabled IN BOOLEAN DEFAULT FALSE,
auto_drop IN BOOLEAN DEFAULT TRUE,
comments IN VARCHAR2 DEFAULT NULL);



Run every Friday. (All three examples are equivalent.)
FREQ=DAILY; BYDAY=FRI;
FREQ=WEEKLY; BYDAY=FRI;
FREQ=YEARLY; BYDAY=FRI;

Run every other Friday.
FREQ=WEEKLY; INTERVAL=2; BYDAY=FRI;

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 10 days.
FREQ=DAILY; INTERVAL=10;

Run daily at 4, 5, and 6PM.
FREQ=DAILY; BYHOUR=16,17,18;

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;

Here are some more complex repeat intervals:
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

Run on the last workday of every month, excluding company holidays. (This example references an existing named schedule called Company_Holidays.)
FREQ=MONTHLY; BYDAY=MON,TUE,WED,THU,FRI; EXCLUDE=Company_Holidays; BYSETPOS=-1

Run at noon every Friday and on company holidays.
FREQ=YEARLY;BYDAY=FRI;BYHOUR=12;INCLUDE=Company_Holidays

A repeat interval of "FREQ=MINUTELY;INTERVAL=2;BYHOUR=17; BYMINUTE=2,4,5,50,51,7;" with a start date of 28-FEB-2004 23:00:00 will generate the following schedule:

SUN 29-FEB-2004 17:02:00
SUN 29-FEB-2004 17:04:00
SUN 29-FEB-2004 17:50:00
MON 01-MAR-2004 17:02:00
MON 01-MAR-2004 17:04:00
MON 01-MAR-2004 17:50:00


A repeat interval of "FREQ=MONTHLY;BYMONTHDAY=15,-1" with a start date of 29-DEC-2003 9:00:00 will generate the following schedule:

WED 31-DEC-2003 09:00:00
THU 15-JAN-2004 09:00:00
SAT 31-JAN-2004 09:00:00
SUN 15-FEB-2004 09:00:00
SUN 29-FEB-2004 09:00:00
MON 15-MAR-2004 09:00:00
WED 31-MAR-2004 09:00:00

Create Sample schedule job,Check invaild objects and write alert log eveny 30 Minutes.

create or replace procedure proc_dba_check2
is
ls_msg varchar2(4000);
kcount number;
BEGIN
select count(*) INTO kcount from dba_objects where status='INVALID';
if kcount=0 then
ls_msg := 'No Invalid objects';
else
ls_msg := 'Invaild Objects Count : ' kcount;
sys.dbms_system.ksdwrt(2, 'ORA- 'ls_msg);
--dbms_output.put_line(ls_msg);
end if;
--END;
--dbms_output.put_line(ls_msg);
end;
/

BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'Invalid_object_job',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN sys.proc_dba_check2; END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=MINUTELY;INTERVAL=30',
end_date => NULL,
enabled => TRUE,
comments => 'Invalid Objects write Alert log ');
End;

BEGIN
DBMS_SCHEDULER.DISABLE('Invalid_object_job');
-- and then
DBMS_SCHEDULER.ENABLE('Invalid_object_job');
END;

SQL> SELECT owner, job_name, enabled FROM dba_scheduler_jobs;