Friday, July 3, 2009

Oracle Scheduler Examples

1) Job that runs every minute available to start immediately

BEGIN
sys.dbms_scheduler.create_job(
job_name => '"TST"."TEST_JOB_PER_MIN"',
job_type => 'PLSQL_BLOCK',
job_action => 'begin
TST12.MY_PROC;
end;',
repeat_interval => 'FREQ=MINUTELY',
start_date => systimestamp at time zone 'UTC',
job_class => 'DEFAULT_JOB_CLASS',
auto_drop => FALSE,
enabled => TRUE);
END;
/

2) Job that runs every 10 minutes starting at 11:45am UTC

BEGIN
sys.dbms_scheduler.create_job(
job_name => '"MYSCHEMA"."MY_JOB_NAME"',
job_type => 'PLSQL_BLOCK',
job_action => 'begin
MY_PROC;
end;',
repeat_interval => 'FREQ=MINUTELY;INTERVAL=2',
start_date => to_timestamp_tz('2010-04-13 11:45:00 0:00', 'YYYY-MM-DD HH24:MI:SS TZH:TZM'),
job_class => 'DEFAULT_JOB_CLASS',
auto_drop => FALSE,
enabled => TRUE);
END;
/

3) Job that runs every 2 hours starting at 11:45am UTC.

BEGIN
sys.dbms_scheduler.create_job(
job_name => '"MYSCHEMA"."MY_JOB_NAME"',
job_type => 'PLSQL_BLOCK',
job_action => 'begin
MY_PROC;
end;',
repeat_interval => 'FREQ=HOURLY;INTERVAL=2',
start_date => to_timestamp_tz('2010-04-13 11:45:00 0:00', 'YYYY-MM-DD HH24:MI:SS TZH:TZM'),
job_class => 'DEFAULT_JOB_CLASS',
auto_drop => FALSE,
enabled => TRUE);
END;
/


4) Job that runs once a day every day and starts at 04/07/2009 03:00 am UTC.

BEGIN
sys.dbms_scheduler.create_job(
job_name => '"TST"."TEST_JOB_PER_DAY"',
job_type => 'PLSQL_BLOCK',
job_action => 'begin
TST12.MY_PROC;
end;',
repeat_interval => 'FREQ=DAILY;BYHOUR=3;BYMINUTE=0;BYSECOND=0',
start_date => to_timestamp_tz('2009-07-04 UTC', 'YYYY-MM-DD TZR'),
job_class => 'DEFAULT_JOB_CLASS',
auto_drop => FALSE,
enabled => TRUE);
END;
/

5) Job that runs every day twice,at specific times UTC
BEGIN
sys.dbms_scheduler.create_job(
job_name => 'MY_USER.JOB_NAME',
job_type => 'PLSQL_BLOCK',
job_action => 'begin
update mytable set x=1;
commit;
end;',
repeat_interval => 'FREQ=DAILY;BYHOUR=16,22;BYMINUTE=10;BYSECOND=0',
start_date => systimestamp at time zone 'UTC',
job_class => 'DEFAULT_JOB_CLASS',
auto_drop => FALSE,
enabled => TRUE);
END;
/

1 comment: