Here, we will learn how to create jobs.
I have prepared table for testing purposes:
SQL> create table test_table(i number,datum date);
Table created.
SQL> create sequence test_seq start with 1 increment by 1 nocache;
Sequence created.
SQL> select * from test_table;
no rows selected
I will create job, which will insert a new row to this table every 2 minutes.
SQL> create table test_table(i number,datum date);
Table created.
SQL> create sequence test_seq start with 1 increment by 1 nocache;
Sequence created.
SQL> select * from test_table;
no rows selected
I will create job, which will insert a new row to this table every 2 minutes.
Here, the SQL to create this job:
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;',
repeat_interval => 'FREQ=MINUTELY;INTERVAL=2',
start_date => systimestamp at time zone 'Europe/Prague',
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'insert row in the table',
auto_drop => FALSE,
enabled => FALSE);
sys.dbms_scheduler.set_attribute( name => '"TEST"."TEST_JOB"', attribute => 'max_run_duration', value => numtodsinterval(30, 'minute'));
sys.dbms_scheduler.enable( '"TEST"."TEST_JOB"' );
END;
After few minutes, you can check the table:
SQL> select i,to_char(datum,'dd.mm.yyyy hh24:mi:ss') "Date" from test_table;
I Date
---------- -------------------
1 29.11.2011 10:21:55
2 29.11.2011 10:23:54
3 29.11.2011 10:25:54
4 29.11.2011 10:27:54
There is dba_scheduler_jobs view, where you can chek if the job is running, how long it was running last time, if there were some failed jobs and a lot more information.
Syntax of the dbms_scheduler:
oracle documentation.
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;',
repeat_interval => 'FREQ=MINUTELY;INTERVAL=2',
start_date => systimestamp at time zone 'Europe/Prague',
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'insert row in the table',
auto_drop => FALSE,
enabled => FALSE);
sys.dbms_scheduler.set_attribute( name => '"TEST"."TEST_JOB"', attribute => 'max_run_duration', value => numtodsinterval(30, 'minute'));
sys.dbms_scheduler.enable( '"TEST"."TEST_JOB"' );
END;
After few minutes, you can check the table:
SQL> select i,to_char(datum,'dd.mm.yyyy hh24:mi:ss') "Date" from test_table;
I Date
---------- -------------------
1 29.11.2011 10:21:55
2 29.11.2011 10:23:54
3 29.11.2011 10:25:54
4 29.11.2011 10:27:54
There is dba_scheduler_jobs view, where you can chek if the job is running, how long it was running last time, if there were some failed jobs and a lot more information.
Syntax of the dbms_scheduler:
oracle documentation.