首页 » ORACLE » dbms_scheduler 指定时间段 job 一例

dbms_scheduler 指定时间段 job 一例

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
打赏

目前这篇文章有1条评论(Rss)评论关闭。

  1. Elana Fredricks | #1
    2011-12-21 at 05:22

    I am extremely cheerful to uncover this page. I would like to say thanks for this great reading. If you can add Reddit button to your web, it can help you to reach a lot more persons online. Kind regards