首页 文章

DBMS_SCHEDULER如何为作业的repeat_interval提供多个计划?

提问于
浏览
1

我有多个时间表,我想将它们全部提供给作业的repeat_interval . 但是,我找不到办法做到这一点 - Oracle文档说这是可能的,但我找不到任何例子 . 任何帮助表示赞赏 . 这是我尝试过的,但它不起作用:

ORA-06512:第2行27418. 00000 - “重复间隔或日历中的语法错误”*原因:重复间隔或日历定义未被识别为有效语法 .

begin
    dbms_scheduler.create_schedule('SCHED1', 
          repeat_interval => 'FREQ=DAILY;BYHOUR=9;BYMINUTE=55');
    dbms_scheduler.create_schedule('SCHED2', 
          repeat_interval => 'FREQ=DAILY;BYHOUR=15;BYMINUTE=15'); 
    DBMS_SCHEDULER.CREATE_JOB(
          JOB_NAME           =>  'SCHED_TEST_JOB',
          JOB_TYPE           =>  'STORED_PROCEDURE',
          JOB_ACTION         =>  'SCHED_TEST_JOB_PROCEDURE',
          START_DATE         =>  SYSDATE,
          REPEAT_INTERVAL    =>  'FREQ=SCHED1,SCHED2;',
          AUTO_DROP          =>  FALSE,
          ENABLED            =>  FALSE);
end;
/

2 回答

  • 0

    我认为你的create_job重复间隔结束时有一个多余的分号 .

    syntax是:

    combined_schedule = schedule_list [";" include_clause][";" exclude_clause] [";" intersect_clause]
    

    ... schedule_list是:

    schedule_list = schedule_clause ("," schedule_clause)
    
  • 0

    您是否希望 combine the schedules 像这样:

    SQL>  BEGIN
      2    dbms_scheduler.create_schedule('sched1',
      3            repeat_interval => 'FREQ=YEARLY;
      4                                BYDATE=0130,0220,0725');
      5    dbms_scheduler.create_schedule('sched2',
      6           repeat_interval => 'FREQ=MONTHLY;
      7                               INTERVAL=2;
      8                               BYMONTHDAY=15;
      9                               BYHOUR=9,17;
     10                               INCLUDE=sched1');
     11  END;
     12  /
    
    PL/SQL procedure successfully completed.
    
    SQL> BEGIN
      2  DBMS_SCHEDULER.create_job (
      3       job_name      => 'test_sched_job_definition',
      4       schedule_name => 'sched2',
      5       job_type      => 'PLSQL_BLOCK',
      6       job_action    => 'BEGIN DBMS_STATS.gather_schema_stats(''SCOTT''); END;',
      7       enabled       => TRUE,
      8       comments      => 'Job defined by existing schedule and inline program.');
      9  END;
     10  /
    
    PL/SQL procedure successfully completed.
    

相关问题