使用DBMS_SCHEDULER包管理計劃任務

使用DBMS_SCHEDULER包管理計劃任務

閱讀導航

• 建立計劃任務:
• 支援的任務類型詳解:
• 樣本
• 手動執行計畫
• 刪除計劃

Dbms_scheduler是Oracle提供建立計劃任務的包，任務類型可以是執行PL\SQL程式、執行外部指令碼、叫用作業系統命令，通常用於建立定期定時的任務，不依賴作業系統，儲存在資料庫內，資料庫遷移時不受影響，發生錯誤有日誌可以查詢，比較方便建立和使用。例如實施資料備份計劃，將資料的備份的腳步儲存在day_backup.sh 中,然後建立計劃定期執行該腳步:
$ vim /oracle/db_backup.sh
#!/bin/sh
ORACLE_SID=sydb;
export ORACLE_SID
ORACLE_HOME=/u01/app/product/11.2.0/db_1;
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH;
export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib;
export LD_LIBRARY_PATH
LOCAL=/EXPORT;
export LOCAL
$ORACLE_HOME/bin/rman target sys/oracle catalog rman/rman log='/tmp/db_backup.log' append <<EOF
run
{
allocate channel dev type disk;
backup incremental level 1 database plus archivelog format '/disk2/backup/sydb/%d_%U_%T.bkp'
tag 'db_backup';
release channel dev;
}
exit;
EOF

注意:這裡要設定環境變數,在這裡吃了好多苦,總之就是檔案許可權和環境變數要設定正確;

建立計劃任務:
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name=>'DB_BACKUP', --job名稱
job_type => 'EXECUTABLE', --job任務類型,請參考2中的解釋
job_action =>'/oracle/db_backup.sh', --job操作
start_date=> to_date('2015-05-19 03:00:00','yyyy-mm-dd hh24:mi:ss'),--執行時間和執行間隔時間為null,則表示立即執行
/*12 天執行一次*/
repeat_interval=>'FREQ=MONTHLY;BYMONTHDAY=15,-1',--執行間隔時間
enabled=>true,--是否啟用
/* next night at 11:00 PM */
comments => 'Nightly incremental backups');
END;
/

 

支援的任務類型詳解:
job_type
This attribute specifies the type of job that you are creating. If it is not specified, an error is generated. The supported values are:
'PLSQL_BLOCK'
This specifies that the job is an anonymous PL/SQL block. Job or program arguments are not supported when the job or program type is PLSQL_BLOCK. In this case, the number of arguments must be 0.
'STORED_PROCEDURE'
This specifies that the job is a PL/SQL or Java stored procedure, or an external C subprogram. Only procedures, not functions with return values, are supported.
'EXECUTABLE'
This specifies that the job is external to the database. External jobs are anything that can be executed from the command line of the operating system. Anydata arguments are not supported with a job or program type of EXECUTABLE. The job owner must have the CREATE EXTERNAL JOB system privilege before the job can be enabled or run.
'CHAIN'
This specifies that the job is a chain. Arguments are not supported for a chain, so number_of_arguments must be 0.

 

樣本

建立一張表，然後建立插入資料的過程:
create table t_insert(cname varchar2(30),cnum number(3,4);
/

create or replace procedure fun_insertdata
/*
功能:向t_insert 表中插入資料
edit:2015-05-15
*/
as
v_sql varchar2(300);
begin
  v_sql:='insert into t_insert(cname,cnum)values(:1,:2)';
  for i in 1..10000 loop
      execute immediate v_sql using 'AAA',i;
  end loop;
    commit;
 end;
/

最後建立一個立即執行的任務
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name=>'INSERT_DATATOTAB', --job名稱
job_type => 'PLSQL_BLOCK', --job任務類型,請參考2中的解釋
job_action =>'--job操作
BEGIN
  fun_insertdata;
 END;
 ',
start_date=>NULL, --執行時間和執行間隔時間如果為null,則表示立即執行
/*12 天執行一次*/
repeat_interval=>NULL,--執行間隔時間
enabled=>true,--是否啟用
/* next night at 11:00 PM */
comments => 'Nightly incremental backups');
END;
/

查詢結果:
SQL> column job_name format a15
SQL> column status format a8
SQL> column SESSION_ID format a7
SQL> column SLAVE_PID format a7
SQL> SELECT JOB_NAME,STATUS,session_id,slave_pid,cpu_used FROM DBA_SCHEDULER_JOB_RUN_DETAILS WHERE JOB_NAME='INSERT_DATATOTAB' and owner='SYS' ;

JOB_NAME        STATUS  SESSION SLAVE_P CPU_USED
--------------- -------- ------- ------- ---------------------------------------------------------------------------
INSERT_DATATOTA SUCCEEDE 173,263 5228    +000 00:00:01.36
B              D        3

所以的scheduler 可以在dbms_schedulers 視圖中查詢到,每個scheduler 任務執行後的結果可以在DBA_SCHEDULER_JOB_RUN_DETAILS中查詢到; 如果想定期執行job可以通過配置repeat_interval，比如每月1,5和最後一天可以這樣配置
repeat_interval=>'FREQ=MONTHLY;BYMONTHDAY=1,5,-1'--執行間隔時間 ,-1表示每月最後一天

上面使用到一個FREQ=,這個表示執行的時間截:

YEARLY 表示每一年
MONTHLY 表示每一月
WEEKLY 表示每一周
DAILY 表示每一天
HOURLY 表示每小時
MINUTELY 表示每分鐘
SECONDLY 表示每秒鐘

so 上面的 (FREQ=MONTHLY;BYMONTHDAY=1,5,-1) MONTHLY表示周期為每月執行,BYMONTHDAY表示每月那幾天執行;

手動執行計畫
SQL> EXEC DBMS_SCHEDULER.RUN_JOB('DB_BACKUP');

注意:預設情況下任務是不啟用的,除非建立計劃時設定了(enabled=>true),so 如果計劃沒有啟用,首先啟用它:
EXEC DBMS_SCHEDULER.enable('DB_BACKUP');

刪除計劃
SQL> exec dbms_scheduler.drop_job('DB_BACKUP');

--Then end(2015-05-18)