Oracle 10g Scheduler

Oracle 10g Scheduler

Oracle 10g 新增加了Scheduler 用于替换普通的job。它调用的包是DBMS_SCHEDULER

下面介绍一下scheduler job 的基本方法：
1. 创建job

BEGIN DBMS_SCHEDULER.CREATE_JOB(
job_name       =>'JOB_NAME_XXX1'
,start_date      =>CAST(to_Date('2015/06/10 00:00:00','yyyy/mm/dd hh24:mi:ss') AS TIMESTAMP)
,repeat_interval =>'FREQ=HOURLY;INTERVAL=3;'
,end_date        =>CAST(to_Date('2099/12/31 00:00:00','yyyy/mm/dd hh24:mi:ss') AS TIMESTAMP)
,job_class       =>'DEFAULT_JOB_CLASS'
,job_type        =>'PLSQL_BLOCK'
,job_action      =>'BEGIN

     <DML/function/procedure>

END;
'
,comments        =>'remark'
,auto_drop => FALSE);
END;

2. 启动job
前面job 创建好之后，默认是disable 的状态，需要执行enable 来激活job.

BEGIN 
DBMS_SCHEDULER.ENABLE('JOB_NAME_XXX1');
END;

3. 禁用job
有时候需要停用job,可以使用disable 去把job 禁用

BEGIN 
DBMS_SCHEDULER.DISABLE('JOB_NAME_XXX1');
END;

4. 执行job
有时候需要手动执行Job,可以使用RUN_JOB

EXEC DBMS_SCHEDULER.RUN_JOB('JOB_NAME_XXX1');

5. 停止job
job 正在执行的时候，可以使用STOP_JOB，实际业务很少使用这个命令。

EXEC DBMS_SCHEDULER.STOP_JOB('JOB_NAME_XXX1');

6. 删除job

EXEC DBMS_SCHEDULER.DROP_JOB('JOB_NAME_XXX1');

7. 修改job
SET_ATTRIBUTE这个命令使用最多。e.g 修改Job频率

BEGIN
  DBMS_SCHEDULER.SET_ATTRIBUTE('JOB_NAME_XXX1','REPEAT_INTERVAL','FREQ=HOURLY;INTERVAL=2;');
END;

 

8. 查询job

 

SELECT * FROM ALL_SCHEDULER_JOBS;

 

参数定义和解释

 

TYPE job IS OBJECT (
   job_name             VARCHAR2(100),
   job_class            VARCHAR2(32),
   job_style            VARCHAR2(11),
   job_template         VARCHAR2(100)
   program_action       VARCHAR2(4000),
   action_type          VARCHAR2(20),
   schedule_name        VARCHAR2(65),
   repeat_interval      VARCHAR2(4000),
   schedule_limit       INTERVAL DAY(2) TO SECOND(6),
   start_date           TIMESTAMP(6) WITH TIME ZONE,
   end_date             TIMESTAMP(6) WITH TIME ZONE,
   event_condition      VARCHAR2(4000),
   queue_spec           VARCHAR2(100),
   number_of_args       NUMBER,
   arguments            JOBARG_ARRAY,
   priority             NUMBER,
   job_weight           NUMBER,
   max_run_duration     INTERVAL DAY(2) TO SECOND(6),
   max_runs             NUMBER,
   max_failures         NUMBER,
   logging_level        NUMBER,
   restartable          VARCHAR2(5),
   stop_on_window_exit  VARCHAR2(5),
   raise_events         NUMBER,
   comments             VARCHAR2(240),
   auto_drop            VARCHAR2(5),
   enabled              VARCHAR2(5),
   follow_default_tz    VARCHAR2(5),
   parallel_instances   VARCHAR2(5),
   aq_job               VARCHAR2(5),
   instance_id          NUMBER);

 

Attribute	Description
job_name	Name of the job
job_class	Name of the job class
job_style	Style of the job: REGULAR LIGHTWEIGHT
job_template	Name of the program. Equivalent to the program_name argument of the CREATE_JOB Procedure.
program_action	Inline action of the job. Equivalent to the job_action argument of the CREATE_JOB Procedure.
action_type	Job action type. Equivalent to the job_type argument of the CREATE_JOB Procedure. Either job_template must be set and program_action and action_type left null, or program_action and action_type must be set and job_template left null.
schedule_name	Name of the schedule that specifies when the job has to execute
repeat_interval	Inline time-based schedule
schedule_limit	Time from the scheduled execution time that the job should be run
start_date	Start date and time of the job
end_date	End date and time of the job
event_condition	Event condition for event-based jobs
queue_spec	Queue specification for event-based jobs
number_of_args	Number of job arguments
arguments	Array of job arguments
priority	Job priority
job_weight	Weight of the job
max_run_duration	Maximum run duration of the job
max_runs	Maximum number of runs before the job is marked as completed
max_failures	Maximum number of failures to tolerate before the job is marked as broken
logging_level	Job logging level
restartable	Indicates whether the job is restartable (TRUE) or not (FALSE)
stop_on_window_exit	Indicates whether the job should be stopped when the window it is running in ends (TRUE) or not (FALSE). Equivalent to the stop_on_window_close job attribute described in the SET_ATTRIBUTE Procedure.
raise_events	State changes on which events should be raised
comments	Comments on the job
auto_drop	If TRUE (the default), indicates that the job should be dropped once completed
enabled	Indicates whether the job should be enabled immediately after creating it (TRUE) or not (FALSE)
follow_default_tz	If TRUE and if the job start_date is null, then when the default_timezone scheduler attribute is changed, the Scheduler recomputes the next run date and time for this job so that it is in accordance with the new time zone. For example, if the job was set to run at 02:00 in the previous time zone, it will run at 02:00 in the new time zone. (If the job start_date is not null, then the time zone for the run date and time for the job is always specified by the time zone of the start_date.) If FALSE, the next start date and time for the job is not recomputed when the default_timezone scheduler attribute is changed. In this case, if the old time zone is three hours earlier than the new time zone, then a job scheduled to run at 02:00 in the old time zone runs at 05:00 in the new time zone.
parallel_instances	For event-based jobs only. If TRUE, on the arrival of the specified event, the Scheduler creates a new lightweight job to handle that event, so multiple instances of the same event-based job can run in parallel.
aq_job	For internal use only.
instance_id	The instance ID of the instance that the job must run on.