I will show how to create schedule.
I will create a schedule which will be initiated every 2 minutes and assign a job that inserts some values to the table. I will use the same table as in my previous example (creating and scheduling jobs).
I will cleanup the table and reset the sequence:
SQL> truncate table test_table;
Table truncated.
SQL> alter sequence test_seq minvalue -5;
Sequence altered.
SQL> alter sequence test_seq increment by -5;
Sequence altered.
SQL> select test_seq.nextval from dual;
NEXTVAL
----------
0
SQL> alter sequence test_seq increment by 1;
Sequence altered.
SQL> select * from test_table;
no rows selected
I will cleanup the table and reset the sequence:
SQL> truncate table test_table;
Table truncated.
SQL> alter sequence test_seq minvalue -5;
Sequence altered.
SQL> alter sequence test_seq increment by -5;
Sequence altered.
SQL> select test_seq.nextval from dual;
NEXTVAL
----------
0
SQL> alter sequence test_seq increment by 1;
Sequence altered.
SQL> select * from test_table;
no rows selected
Here, the SQL for creating the schedule:
BEGIN
sys.dbms_scheduler.create_schedule(
repeat_interval => 'FREQ=MINUTELY;INTERVAL=2',
start_date => systimestamp at time zone 'Europe/Prague',
comments => 'just a test schedule',
schedule_name => '"TEST"."TEST_SCHEDULE"');
END;
I will assign a job to this schedule:
BEGIN
sys.dbms_scheduler.create_schedule(
repeat_interval => 'FREQ=MINUTELY;INTERVAL=2',
start_date => systimestamp at time zone 'Europe/Prague',
comments => 'just a test schedule',
schedule_name => '"TEST"."TEST_SCHEDULE"');
END;
I will assign a job to this schedule:
Here, you can see, that its working:
SQL> select i,to_char(datum,'dd.mm.yyyy hh24:mi:ss') "Date" from test_table;
I Date
---------- -------------------
1 29.11.2011 13:34:15
2 29.11.2011 13:36:14
3 29.11.2011 13:38:14
Here, the SQL for the job using the schedule:
BEGIN
sys.dbms_scheduler.create_job(
job_name => '"TEST"."TEST_JOB"',
job_type => 'PLSQL_BLOCK',
job_action => 'begin
insert into test_table values(test_seq.nextval,sysdate);
commit;
end;',
schedule_name => '"TEST"."TEST_SCHEDULE"',
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'schedules test',
auto_drop => FALSE,
enabled => TRUE);
END;
SQL> select i,to_char(datum,'dd.mm.yyyy hh24:mi:ss') "Date" from test_table;
I Date
---------- -------------------
1 29.11.2011 13:34:15
2 29.11.2011 13:36:14
3 29.11.2011 13:38:14
Here, the SQL for the job using the schedule:
BEGIN
sys.dbms_scheduler.create_job(
job_name => '"TEST"."TEST_JOB"',
job_type => 'PLSQL_BLOCK',
job_action => 'begin
insert into test_table values(test_seq.nextval,sysdate);
commit;
end;',
schedule_name => '"TEST"."TEST_SCHEDULE"',
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'schedules test',
auto_drop => FALSE,
enabled => TRUE);
END;
When you create a schedule, Oracle provides access to PUBLIC. Thus, all users can use your schedule, without any explicit grant of privileges to do so.
If you delete the schedule, all dependat jobs will be disabled.
If you delete the schedule, all dependat jobs will be disabled.