oracle使用DBMS_SCHEDULER排程作業

dbms_scheduler包的功能比dbms_job包強大很多，但是很多初學者直接被它的複雜性嚇跑了，跟著我，只需幾分鐘就會用了。

三個概念

大多數人看到這個包裡的函式和函式裡眾多的引數，就開始暈菜了，不要被這些表象迷惑了，其實這些東西都是圍繞著三個基本概念，schedule，program和job。oracle是為了複用的目的，提煉出了排程的這三個要素，弄懂這三個要素，立刻豁然開朗。

schedule

schedule表示排程計劃表。排程從什麼時間開始被排程，什麼時候結束，以什麼頻度排程。使用DBMS_SCHEDULER.CREATE_SCHEDULE過程建立schedule。

 begin
    DBMS_SCHEDULER.CREATE_SCHEDULE (
      schedule_name     => ¨daily_schedule¨,
      start_date        => SYSDATE,
      repeat_interval   => ¨FREQ=DAILY ; INTERVAL=1¨,
      comments          => ¨every one day¨);
    END;
    /

其中repeat_interval引數，支援兩種格式：

• dbms_job裡的interval格式，建議讓這種晦澀語法見鬼去吧
• 日曆表示式（linux系統的crontab使用的格式）

日曆表示式分為三部分: 第一部分是頻率，也就是”FREQ”這個關鍵字，它是必須指定的; 第二部分是時間間隔，也就是”INTERVAL”這個關鍵字，取值範圍是1-999. 它是可選的引數; 第三部分是附加的引數,可用於精確地指定日期和時間,它也是可選的引數,下面這些值都是合法的:

BYMONTH,BYWEEKNO,BYYEARDAY,BYMONTHDAY,BYDAY 
BYHOUR,BYMINUTE,BYSECOND

看幾個例子就會用了

每隔2小時執行一次
repeat_interval => 'FREQ=HOURLY; INTERVAL=2'

每天執行一次
repeat_interval => 'FREQ=DAILY'

每週的1,3,5執行
repeat_interval => 'FREQ=WEEKLY; BYDAY=MON,WED,FRI"

每年的3,6,9,12月的18號執行
repeat_interval => 'FREQ=YEARLY; BYMONTH=MAR,JUN,SEP,DEC; BYMONTHDAY=18'

另外使用dbms_scheduler.evaluate_calendar_string可以方便的計算出什麼時候執行該排程。

program

program表示排程應該做什麼事情，是對程式的抽象。使用DBMS_SCHEDULER.CREATE_PROGRAM建立program

 BEGIN
    DBMS_SCHEDULER.CREATE_PROGRAM (
       program_name           => ¨time_synchronization¨,
       program_action         => ¨/sbin/ntpdate 128.59.67.100¨,
       program_type           => ¨EXECUTABLE¨,
       enabled                => TRUE);
 END;
    /

排程現在可以支援呼叫外部程式了，這點很強大。
目前程式支援三種型別:

• PL/SQL塊: PLSQL_BLOCK,
• 儲存過程: STORED_PROCEDURE
• 外部程式: EXECUTABLE， 外部程式可以是一個shell指令碼,也可以是作業系統級別的命令。

program_action: 根據program_type的不同，program_action有不同的含義。

• program_type是儲存過程，就需要指定儲存過程的名字;
• program_type是PL/SQL塊，就需要輸入完整的PL/SQL程式碼;
• program_type是外部程式，就需要輸入script的名稱或者作業系統的指令名

job

job表示按照指定的schedule，執行指定program，完成使用者指定的工作。使用DBMS_SCHEDULER.CREATE_JOB建立job。

SQL> BEGIN
  2  DBMS_SCHEDULER.CREATE_JOB (
  3     job_name           =>  ¨time_synchron¨,
  4     program_name       =>  ¨time_synchronization¨,
  5     schedule_name      =>  ¨daily_schedule¨,
  6     enabled            =>  true);
  7  END;
  8  /

排程的相關操作

作業相關操作

一般情況下是如果你設定了job的enable是true的話，oracle會按照你的計劃，定時呼叫你的job，不需要手動執行。如果臨時需要馬上排程job也是可以的。

exec dbms_scheduler.run_job(¨time_synchron¨);

如果覺得沒有必要繼續執行這個job了，可以停止該job，讓oracle以後不要再繼續排程了。

exec dbms_scheduler.stop_job(¨time_synchron¨);

檢視作業相關情況

Job 每執行一次，無論成功或失敗，均會[DBA|ALL|USER]_SCHEDULER_JOB_LOG中生成一條對應的記錄(前提是LOGGING_LEVEL屬性值未設定為DBMS_SCHEDULER.LOGGING_OFF)，job的詳細資訊可以通過[DBA|ALL|USER]_SCHEDULER_JOB_RUN_DETAILS檢視檢視。

---

示例

BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
            job_name => '"CHINA_TEST"."JOB_AUTOMODIFYINFO"',
            job_type => 'STORED_PROCEDURE',
            job_action => 'CHINA_TEST.AUTOMODIFYINFO',
            number_of_arguments => 0,
            start_date => TO_TIMESTAMP_TZ('2018-08-20 10:26:52.000000000 ASIA/SHANGHAI','YYYY-MM-DD HH24:MI:SS.FF TZR'),
            repeat_interval => 'FREQ=MINUTELY;INTERVAL=2',
            end_date => NULL,
            enabled => FALSE,
            auto_drop => FALSE,
            comments => 'JOB作業說明');



    DBMS_SCHEDULER.SET_ATTRIBUTE( 
             name => '"CHINA_TEST"."JOB_AUTOMODIFYINFO"', 
             attribute => 'logging_level', value => DBMS_SCHEDULER.LOGGING_OFF);



    DBMS_SCHEDULER.enable(
             name => '"CHINA_TEST"."JOB_AUTOMODIFYINFO"');
END;