Oracle Scheduler - Postponed job
前面的例子中,可以使用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秒
posted on 2012-01-30 18:21 wait4friend 阅读(205) 评论(0) 编辑 收藏 举报