Oracle 排程程式（scheduler）

在11g中，Oracle提供了一個建立的Scheduler特性，幫助将作業實作自動化。它還可以幫助你控制資源的利用與并可以将資料庫中的作業按優先順序執行。傳統的dbms_jobs的一個限制是它隻能排程基于PL/SQL的作業，不能用來排程作業系統的可執行檔案或腳本。

Scheduler包括如下權限：

scheduler_admin角色包含所有的scheduler系統權限，授權如下：

SQL> grant scheduler_admin to djp01

  2  /

Grant succeeded.

SQL>

manage scheduler系統權限允許做以下工作：‘

建立、删除和更改作業類，視窗和視窗組。

停止作業

提前啟動和停止視窗（關于這些部件在下文中進行介紹）。

授權如下：

SQL> grant manage scheduler to djp01

  2  /

Grant succeeded.

SQL>

create job系統權限允許做如下工作：

建立作業（job）、進度表（schedule）、程式（program）、鍊（chain）和事件（event）。

（關于這些部件在下文中會進行介紹），授權如下：

SQL> grant create job to djp01

  2  /

Grant succeeded.

SQL>

如果要執行的程式在不同的模式下，那麼必要擁有執行相應模式下的程式的執行權限，如下execute any procedure。

Scheduler包括如下基本部件：

（1）作業（job）

作業是一個計劃執行一次或多次的任務。例子如下：

SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss'

  2  /

Session altered.

SQL> alter session set nls_timestamp_tz_format = 'yyyy-mm-dd hh24:mi:ss.ff'

  2  /

Session altered.

SQL> alter session set nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff'

  2  /

Session altered.

SQL>

說明：在使用排程程式時，會涉及一些時間的相關資訊，我這裡進行一些時間格式的設定友善使用。

SQL> create table emp_bak

  2  as

  3  select *

  4  from employees

  5  where 1 = 0

  6  /

Table created.

SQL> 

SQL> begin

  2     dbms_scheduler.create_job(

  3        job_name=>'test_job',

  4        job_type=>'PLSQL_BLOCK',

  5        job_action=>'insert into emp_bak select * from employees where rownum <= 200;',

  6        start_date=>'2012-12-09 14:40:00',

  7        repeat_interval=>'FREQ=DAILY;INTERVAL=2',

  8        end_date=>'2012-12-09 20:00:00',

  9        comments=>'insert old employees into the emp_bak table',

 10        auto_drop=>false,

 11        enabled=>true);

 12  end;

 13  /

PL/SQL procedure successfully completed.

SQL>

說明：job_name指定作業的名稱；job_type指定要建立的作業的類型，其值包括PLSQL_BLOCK（PL/SQL塊），STORED_PROCEDURE（存儲過程），EXECUTABLE（可執行檔案或Java程式）。job_action指定作業要執行過程，指令或腳本。start_date與end_date指定作業的啟動與結束時間。comments用于給目前作業添加注釋。enabled指定建立作業時，是否啟動或禁用作業，預設值為false，表示禁用；值為true，表啟用。auto_drop指定該作業執行完成後，是否自動将其删除，預設值為true，表示執行完成後自動删除該作業。repeat_interval指定作業執行的頻率，FREQ=DAILY;INTERVAL=2表示每天運作一次該作業。該值是一個月曆表達式（calendaring expression）由三個部分組成，Frequency： 這是表達式必須包含的部分，用FREQ指定，可能取的值為YEARLY，MONTHLY，WEEKLY，DAILY，HOURLY，MINUTELY和SECONDLY。repeat interval：這個時間間隔由INTERVAL關鍵字辨別，表示執行的頻率。specific：提供一個作業何時運作的詳細資訊，可能取值為：BYMONTH，BYWEEKNO，BYYEARDAY，BYMONTHDAY，BYDAY，BYHOUR，BYMINUTE和BYSECOND。例如：BYMONTHDAY表示每月的某一天，BYDAY表示每周的某一天。以下是典型的月曆表達式：

FREQ=DAILY；INTERVAL=3；每三天執行一次。

FREQ=HOURLY；INTERVAL=2；每隔一小時執行一次。

FREQ=WEEKLY，BYDAY=SUN；每個星期日執行一次。

FREQ=WEEKLY；INTERVAL=2，BYDAY=FRI；每隔一周的周五執行一次。

FREQ=MONTHLY；BYMONTHDAY=1；每月最後一天執行一次。

FREQ=MINUTELY；INTERVAL=30；每半個小時執行一次。

下面我們看一下執行的情況：

SQL> select count(*)

  2  from emp_bak

  3  /

  COUNT(*)

----------

       200

SQL>

上述排程程式的作業成功運作，下面我們看一些該作業的資訊：

SQL> select job_style,job_type,job_action,program_name,state

  2  from dba_scheduler_jobs

  3  where job_name = upper('test_job')

  4  /

JOB_STYLE              JOB_TYPE

---------------------- --------------------------------

JOB_ACTION

--------------------------------------------------------------------------------

PROGRAM_NAME

--------------------------------------------------------------------------------

STATE

------------------------------

REGULAR                PLSQL_BLOCK

insert into emp_bak select * from employees where rownum <= 200;

COMPLETED

SQL>

狀态顯示，該作業執行完成，該作業的類型為REGULAR（規律性的）。

作業的其他一些管理：

禁用一個作業：

SQL> exec dbms_scheduler.disable('test_job');

PL/SQL procedure successfully completed.

SQL>

激活一個作業：

SQL> exec dbms_scheduler.enable('test_job');

PL/SQL procedure successfully completed.

SQL>

運作一個作業：

SQL> truncate table emp_bak;

Table truncated.

SQL> exec dbms_scheduler.run_job('test_job');

PL/SQL procedure successfully completed.

SQL> select count(*)

  2  from emp_bak

  3  /

  COUNT(*)

----------

       200

SQL>

說明：該運作相當于是手工執行一次相應的程式。

停止一個作業：

SQL> exec dbms_scheduler.stop_job('test_job');

如果一個作業不在運作狀态，它将會出現ORA-27366錯誤。

删除一個作業：

SQL> exec dbms_scheduler.drop_job('test_job');

PL/SQL procedure successfully completed.

SQL>

（2）進度表（schedule）

進度表是資料庫執行一個作業的時間及頻率的說明。假如有一些作業都是在大緻相同的時間内運作的，那麼，可以使用一個進度表，可以化這些作業的建立與管理。例子如下：

SQL> begin

  2     dbms_scheduler.create_schedule(

  3        schedule_name=>'test_schedule',

  4        start_date=>systimestamp,

  5        repeat_interval=>'FREQ=MINUTELY;INTERVAL=30',

  6        end_date=>systimestamp+3,

  7        comments=>'Every 30 minute');

  8  end;

  9  /

PL/SQL procedure successfully completed.

SQL>

下面我們檢視該進度表的建立資訊：

SQL> select start_date,end_date,schedule_type

  2  from dba_scheduler_schedules

  3  where schedule_name = upper('test_schedule')

  4  /

START_DATE

---------------------------------------------------------------

END_DATE

---------------------------------------------------------------

SCHEDULE_TYPE

----------------

2012-12-09 17:22:43.781000

2012-12-12 17:22:43.000000

CALENDAR

SQL>

可以對已經建立的進度表進行相關屬性的更改，如下：

SQL> begin

  2     dbms_scheduler.set_attribute(

  3        name=>'test_schedule',

  4        attribute=>'start_date',

  5        value=>systimestamp);

  6  end;

  7  /

PL/SQL procedure successfully completed.

SQL>

對已經有的進度表，我們可以進行删除，如下：

SQL> exec dbms_scheduler.drop_schedule('test_schedule');

PL/SQL procedure successfully completed.

SQL>

（3）程式（program）

程式包括關于一個scheduler作業的中繼資料。程式包括程式名，程式類型以及程式的動作。它是一個過程或是可執行腳本的實際名稱。例子如下：

SQL> begin

  2     dbms_scheduler.create_program(

  3        program_name=>'test_program',

  4        program_type=>'STORED_PROCEDURE',

  5        program_action=>'auto_archive_emp',

  6        enabled=>true,

  7        comments=>'use to execute the procedure of auto_archive_emp');

  8  end;

  9  /

PL/SQL procedure successfully completed.

SQL>

說明：program_type與program_action同上述的job_type,job_action含義一樣，這裡不再說明。enabled表示該程式是否被啟用，預設值為false，表示禁用。

我們可以用如下的方法對程式進行啟用或禁用：

禁用一個程式：

SQL> exec dbms_scheduler.disable('test_program');

PL/SQL procedure successfully completed.

SQL>

啟用一個程式：

SQL> exec dbms_scheduler.enable('test_program');

PL/SQL procedure successfully completed.

SQL>

同樣，我們也可以進行删除，如下：

SQL> exec dbms_scheduler.drop_program('test_program');

PL/SQL procedure successfully completed.

SQL>

對程式相關資訊的檢視可以使用dba_scheduler_programs資料字典。

（4）鍊（chain）

可以使用排程程式鍊的概念将相關的程式連結在一起。是以，一個程式的成功運作，可能是以另外的程式成功運作為基礎；還可以是基于一個鍵而非程式啟動作業。鍊中連續的位置稱為鍊的“步驟（step）”，每個步驟指向另一個鍊，程式或事件。由于鍊使用Oracle Streams Rules Engine（Oracle流規則引擎對象）。是以使用者必須具有create job 和Rules Engine權限才能建立一個鍊。例子如下：

進行如下授權：

SQL> begin

  2     dbms_rule_adm.grant_system_privilege(

  3        dbms_rule_adm.create_rule_obj,'djp01');

  4     dbms_rule_adm.grant_system_privilege(

  5        dbms_rule_adm.create_rule_set_obj,'djp01');

  6     dbms_rule_adm.grant_system_privilege(

  7        dbms_rule_adm.create_evaluation_context_obj,'djp01');

  8  end;

  9  /

PL/SQL procedure successfully completed.

SQL>

建立如下：

SQL> begin

  2     dbms_scheduler.create_chain(

  3        chain_name=>'test_chain',

  4        comments=>'A shain test');

  5  end;

  6  /

PL/SQL procedure successfully completed.

SQL>

定義鍊步驟：

SQL> begin

  2     dbms_scheduler.define_chain_step(

  3        chain_name=>'test_chain',

  4        step_name=>'test_step1',

  5        program_name=>'test_program1');

  6     dbms_scheduler.define_chain_step(

  7        chain_name=>'test_chain',

  8        step_name=>'test_step2',

  9        program_name=>'test_program2');

 10  end;

 11  /

PL/SQL procedure successfully completed.

SQL>

說明：鍊步驟可以指向一個程式、鍊或事件。使用不同的參數進行辨別。

為了使用鍊有效的運作，必須給鍊添加規則，用于确定步驟何時運作并指定運作條件。如下：

SQL> begin

  2     dbms_scheduler.define_chain_rule(

  3        chain_name=>'test_chain',

  4        condition=>'true',

  5        action=>'start test_step1');

  6     dbms_scheduler.define_chain_rule(

  7        chain_name=>'test_chain',

  8        condition=>'test_step1 completed',

  9        action=>'start test_step2');

 10     dbms_scheduler.define_chain_rule(

 11        chain_name=>'test_chain',

 12        condition=>'test_step2 completed',

 13        action=>'end');

 14  end;

 15  /

PL/SQL procedure successfully completed.

SQL>

說明：在規則中，我們按照每一步的先後順序進行定義。如果每個步驟中都事務的先後關系，使用鍊是一個不錯的選擇。

在上述步驟完成之後，我們還需要對鍊進行啟用，如下：

SQL> exec dbms_scheduler.enable('test_chain');

PL/SQL procedure successfully completed.

SQL>

這時，我們就可以在作業（job）中使用該鍊了（job_type指定為chain，job_action指定對應鍊的名稱，如test_chain）。

我們還可以使用如下方式運作：

SQL> begin

  2     dbms_scheduler.run_chain(

  3        chain_name=>'test_chain',

  4        start_steps=>'test_step1,test_step2');

  5  end;

  6  /

PL/SQL procedure successfully completed.

SQL>

（5）事件（event）

Scheduler使用Oracle Stream Advanced Qeueing（Oracle流進階隊列）觸發事件并啟動基于事件的資料庫作業。事件是一個應用程式或是程序注意到某個事件或動作時發出的一條消息。有兩種類型的事件：Scheduler引發的事件和應用程式引發的事件。Scheduler引發的事件由Scheduler運作中的變化而導緻，比如Scheduler作業成功完成是一個事件。應用程式引發的事件由Scheduler為啟動一個作業而使用或是消費。

使用基于事件的作業，隻要在create_job過程上指定event_condition與queue_spec即可。但是事件的定義涉及到了，dbms_aqadm程式包的一些使用。我檢視了dbms_aqadm包的聲明，沒有相關的注釋，之後試了幾次，沒有達到效果，這個問題有待解決。

下面我們來看幾個綜合使用的例子：

SQL> begin

  2     dbms_scheduler.create_job(

  3        job_name=>'test_job1',

  4        program_name=>'test_program1',

  5        schedule_name=>'test_schedule',

  6        enabled=>true,

  7        auto_drop=>false,

  8        comments=>'use program and schedule to create job');

  9  end;

 10  /

PL/SQL procedure successfully completed.

SQL>

說明：在上述作業的建立中，我使用了程式與進度表。如果要進行調整，可以調整相應的程式與進行度即可。

SQL> begin

  2     dbms_scheduler.create_job(

  3        job_name=>'test_job2',

  4        schedule_name=>'test_schedule',

  5        job_type=>'chain',

  6        job_action=>'test_chain',

  7        enabled=>true,

  8        auto_drop=>false,

  9        comments=>'use chain to create job');

 10  end;

 11  /

PL/SQL procedure successfully completed.

SQL>

說明：在建立job時，我們兩樣也可以指定一個鍊。如果要指定鍊，那麼job_type為chain，job_action為所建立的鍊名。