的例子中，可以使用plsql语法指定repeat_interval

repeat_interval => 'sysdate + interval ''10'' second'

=================================================================

在 指定时间，前一个instance还在运行的情况下，会发生什么？

=================================================================

被 执行的sp随机停止8-12秒( p_2使用了dbms_lock，这个必须有权限)

grant execute on dbms_lock to xxx;

create or replace procedure p_2 is

  l_c integer;

begin

  select trunc(dbms_random.value(8,12)) into l_c from dual;

  insert into scheduler_test(x,y) values(to_char(sysdate,'yyyymmdd hh24:mi:ss'),l_c);

  commit;

  sys.dbms_lock.sleep(l_c);

end;

/

每10 秒的整点运行一次

declare

  l_x integer := 0;

begin

  dbms_scheduler.create_job(job_name        => 'ttt',

                            job_type    => 'stored_procedure',

                            job_action => 'p_2',

                            start_date      => systimestamp,

                            repeat_interval => 'freq=minutely;interval=1;bysecond=0,10,20,30,40,50');

  dbms_scheduler.enable('ttt');

end;

/

SQL> col x for a20

SQL> col y for a5

SQL> select * from scheduler_test order by 1;

 

X                        Y

-------------------- -----

20070802 10:40:40       10

20070802 10:40:50       10

20070802 10:41:01       10

20070802 10:41:11        8

20070802 10:41:20        9

20070802 10:41:30        9

20070802 10:41:40       10

20070802 10:41:50       11

从 结果分析看出，job会被延迟执行。如上面第3条应该在0秒执行，但是被延迟到01秒