先来个例子:
一、在PLSQL中创建表:
create table system.TEST
(
CARNO VARCHAR2(30),
CARINFOID NUMBER
)
二、在PLSQL中创建存储过程:
创建序列:
create sequence s_CarInfoID
increment by 1
start with 100
maxvalue 999999999;
AS
carinfo_id number;
BEGIN
select s_CarInfoID.nextval into carinfo_id
from dual;
insert into system.test(carno,carinfoid) values(carinfo_id,'123');
commit;
end pro_test;
三、在SQL命令窗口中启动任务:
在SQL>后执行:
VARIABLE jobno number;
begin
DBMS_JOB.SUBMIT(:jobno,
'pro_test;',
SYSDATE,'sysdate+1/24/12');
commit;
end;
/
提交后提示:
PL/SQL procedure successfully completed
jobno
四、跟踪任务的情况(查看任务队列):
SQL> select job,next_date,next_sec,failures,broken from user_jobs;
JOB NEXT_DATE NEXT_SEC FAILURES BROKEN
---------- ----------- ---------------- ---------- ------
1 2008-2-22 ?01:00:00 0 N
说明有一个任务存在了。
五、停止/删除 已经启动的定时任务:
先执行select job,next_date,next_sec,failures,broken from user_jobs;
以查看定时任务的job号。在SQL>删除定时任务:
begin
dbms_job.remove(1);
commit;
end;
/
停止定时任务:
begin
dbms_job.broken(27,true);
commit;
end;
六、查看进程数:
show parameter job_queue_processes;
必须>0,否则执行下面的命令修改:
alter system set job_queue_processes=5;
七、再创建一个任务(每5分钟执行一次):
variable jobno number;
begin
dbms_job.submit(:jobno, 'pro_test;',
sysdate,'sysdate+1/24/12');
commit;
end;
/
八、创建一个任务的完整的格式是:
参数:
job参数是由Submit()过程返回的binary_ineger。这个值用来唯一标识一个工作;
what参数是将被执行的PL/SQL代码块;
next_date参数指识何时将运行这个工作。写Job的时候可以不指定该值;
interval参数何时这个工作将被重执行。
间隔/interval是指上一次执行结束到下一次开始执行的时间间隔,当interval设置为null时,该job执行结束后,就被从队列中删除。假如我们需要该job周期性地执行,则要用‘sysdate+m’表示。
begin
sys.dbms_job.submit(job => :jobno,
what => 'pro_test;',
next_date => to_date('21-02-2008 17:37:26', 'dd-mm-yyyy hh24:mi:ss'),
interval => 'sysdate+1/24/12');
commit;
end;
九、 执行select job,next_date,next_sec,failures,broken from user_jobs;
结果:
JOB NEXT_DATE NEXT_SEC FAILURES BROKEN
1 1 2008-2-22 AM 01:00:00 01:00:00 0 N
2 2 2008-2-21 PM 05:42:45 17:42:45 0 N
3 3 2008-2-21 PM 05:42:45 17:42:45 0 N
常用信息:
十、总结
关于job运行时间
1:每分钟执行
2:每天定时执行
例如:每天的凌晨1点执行
3:每周定时执行
例如:每周一凌晨1点执行
4:每月定时执行
例如:每月1日凌晨1点执行
5:每季度定时执行
例如每季度的第一天凌晨1点执行
6:每半年定时执行
例如:每年7月1日和1月1日凌晨1点
7:每年定时执行
例如:每年1月1日凌晨1点执行
job的运行频率设置
1.每天固定时间运行,比如早上8:10分钟:Trunc(Sysdate+1) + (8*60+10)/24*60
2.Toad中提供的:
每天:trunc(sysdate+1)
每周:trunc(sysdate+7)
每月:trunc(sysdate+30)
每个星期日:next_day(trunc(sysdate),'星期日')
每天6点:trunc(sysdate+1)+6/24
半个小时:sysdate+30/(24*60)
3.每个小时的第15分钟运行,比如:8:15,9:15,10:15…:trunc(sysdate,'hh')+(60+15)/(24*60)
。
二、jobs
使用dbms_scheduler需要具有create job权限,对定时任务一些操作需要具有MANAGE SCHEDULER权限,如:dbms_scheduler.stop_job('my_job_test',true);
BEGIN
dbms_scheduler.create_job(job_name => 'my_job_test',
job_type => 'STORED_PROCEDURE',
job_action => 'my_test',
start_date => sysdate,
repeat_interval => 'sysdate + 1/1440',
enabled => TRUE,
comments => 'test');
end;
参数介绍:
job_name:job名字
job_type:job类型,支持三种类型:
1)PLSQL_BLOCK——PL/SQL语句块;
2)STORED_PROCEDURE——存储过程;
3)EXECUTABLE——外部程序(外部程序可以是一个shell脚本,也可以是操作系统级别的指令)。
job_action:根据job_type的不同,job_action有相对应的内容。
number_of_arguments:参数个数。
start_date:执行开始时间。
repeat_interval:指定job执行频率(如每分钟执行一次、每天执行一次等)。
end_date:执行结束时间。
job_class:jobclass的名字。
enabled:指定是否自动激活job,为true代表自动激活,false代表不激活。
auto_drop:执行完是否自动drop
comments:对于job的简单说明
定时器执行,调用存储过程创建表成功了,不需要显示的授权grant create table to user,只需要存错过程定义为authid current_user即可。个人觉得dbms_job在调用authid current_user的存储过程的时候,未能调用到用户具有的角色的权限,这或许是dbms_job的一个bug。
简单介绍下dbms_scheduler关于定时任务的一些常用过程:
1) dbms_scheduler.run(jobName) 运行job
2) dbms_scheduler.stop_job(jobName,force) 停止job,force默认为false,Oracle建议false停止失败情况下,使用true,且使用true需要有manage scheduler权限
3) dbms_scheduler.drop_job(jobName) 删除job
4) dbms_scheduler.enable(jobName) 打开job
5) dbms_scheduler.disable(jobName,force) 禁用job,force参数用于dependencies,如果TRUE,即使其他对象依赖于它,操作也能成功
相关视图
1) user_scheduler_jobs 查看job信息
2) User_Scheduler_Job_Log job job日志
3) user_scheduler_job_run_details job运行日志
4) user_scheduler_running_jobs 正在运行的job