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)
