ANBOB™

dbms_scheduler 是从10g开始用来替换dbms_job,它除了调用procedure还可能调用os命令或无名pl/sql块，而且在调用上更灵活，下面一例

问题:
怎么实现每天8点自动执行job，之后每小时执行一次，一直到17:00执行最后一次？

实验

create table act_sess_history(cnt number(10),gettime date default sysdate);

sys@ORCL> l
  1  create or replace procedure gather_session
  2   is
  3   v_actses int;
  4   begin
  5   select count(*) into v_actses from v$session where status='ACTIVE';
  6   insert into act_sess_history (cnt) values (v_actses);
  7   commit;
  8*  end;
sys@ORCL> /

Procedure created.

sys@ORCL> exec gather_session;

PL/SQL procedure successfully completed.

sys@ORCL> select * from act_sess_history;

       CNT GETTIME
---------- -------------------
        17 2011-09-26 13:59:12

sys@ORCL> begin
  2  dbms_scheduler.create_job(
  3  job_name  => 'gather_active_sessions',
  4  job_type  => 'STORED_PROCEDURE',
  5  job_action => 'GATHER_SESSION',
  6  start_date  => trunc(sysdate+1)+8/24,
  7  REPEAT_INTERVAL =>'FREQ=DAILY;BYHOUR=8,9,10,11,13,14,15,16,17',
  8  enabled=>true,
  9  comments => 'gather active session count every day work time'
 10  );
 11  end;
 12  /

PL/SQL procedure successfully completed.

sys@ORCL> exec dbms_scheduler.enable('GATHER_ACTIVE_SESSIONS');

PL/SQL procedure successfully completed.

sys@ORCL> exec dbms_scheduler.disable('GATHER_ACTIVE_SESSIONS');

PL/SQL procedure successfully completed.

sys@ORCL> exec dbms_scheduler.run_job('GATHER_ACTIVE_SESSIONS');

PL/SQL procedure successfully completed.

sys@ORCL> select * from act_sess_history;

       CNT GETTIME
---------- -------------------
        17 2011-09-26 13:59:12
        17 2011-09-26 15:23:55

打赏

微信扫一扫，打赏作者吧～