使用DBMS_SCHEDULER包管理计划任务

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)

使用DBMS_SCHEDULER包管理计划任务

标签：