一、描述
常要oracle数据库定时的自动执行一些脚本,或做数据库备份,或做数据的提炼,或做数据库的性能优化,包括重建索引等等的工作,这时需要用到一个函数dbms_job.submit,来完成Oracle定时器Job时间的处理上。使用dbms_job.submit这个函数,我们只需要考虑两个事情:安排某一任务,和定制一个执行任务的时间点。
--1. 创建测试任务
--(1)创测试表
create table job_test(updatetime date);
--(2)创建JOB
declare v_job_no number;
begin
dbms_job.submit(v_job_no, 'insert into job_test values(sysdate);', sysdate, 'sysdate+1/1440'); --马上会执行一次,以后每间隔1分钟执行一次
commit;
end;
---总结
创建的 v_job_no (任务号)由sys.jobseq序列生成,无需理会
--如果是SQL/plus,需要加冒号
variable test_job_really number;
begin
dbms_job.submit(:test_job_really,'test_jobproce;',sysdate,'sysdate+1/1440');
commit;
end;
--------------------------
PL/sql的写法
--declare
declare test_job_really number;
begin
dbms_job.submit(test_job_really,'test_jobproce;',sysdate,'sysdate+1/1440'); //test_job_really鍓嶉潰鍘绘帀锛氬彿
commit;
end;
--------------------------
SYSDATE+1 是指当前时间执行一次,24小时候又执行一次;
而
'
TRUNC(SYSDATE + 1)
' 是每天午夜执行,因为用trunc,时间变成0:0:0
--查看
select * from sys.user_jobs;
--停止或恢复
exec dbms_job.broken(:job) 停止
exec dbms_job.broken(186,true) //标记位broken
exec dbms_job.broken(186,false)//标记为非broken
exec dbms_job.broken(186,false,next_day(sysdate,'monday')) //标记为非broken,指定执行时间
例如:
begin
dbms_job.broken(31365,true);
commit;
end;
--移除
exec dbms_job.remove(:job);删除
例如:
begin
dbms_job.remove(31345);
commit;
end;
--没有权限
delete from sys.user_jobs t where t.job = 31345 ;
二、案例
下面是函数dbms_job.submit使用方法:
其中:
●job:输出变量,是此任务在任务队列中的编号;
●what:执行的任务的名称及其输入参数;
●next_date:任务执行的时间;
●interval:任务执行的时间间隔。
其中Interval这个值是决定Job何时,被重新执行的关键;当interval设置为null时,该job执行结束后,就被从队列中删除。假如我们需要该job周期性地执行,则要用‘sysdate+m’表示。如何更好地确定执行时间的间隔需要我们掌握一个函数TRUNC。
1.TRUNC(for dates)
TRUNC函数为指定元素而截去的日期值。
其具体的语法格式如下:
TRUNC(date[,fmt])
其中:
date 一个日期值
fmt 日期格式,该日期将由指定的元素格式所截去。忽略它则由最近的日期截去
下面是该函数的使用情况:
1)按年截尾
select TRUNC(TO_DATE('2008-03-01 08:23','yyyy-mm-dd hh:mi'),'yyyy') from dual
-----------------------------------------------------------
2008-1-1
2)按月截尾
select TRUNC(TO_DATE('2008-03-01 08:23','yyyy-mm-dd hh:mi'),'mm') from dual
--------------------------------------------------------
2008-3-1
3)按日截尾
select TRUNC(TO_DATE('2008-03-01 08:23','yyyy-mm-dd hh:mi'),'dd') from dual
----------------------------------------------------------------------
2008-3-1
4)按时截尾
select TRUNC(TO_DATE('2008-03-01 08:23','yyyy-mm-dd hh:mi'),'hh') from dual
----------------------------------------------------------------------
2008-3-1 8:00:00
5)按分截尾
select TRUNC(TO_DATE('2008-03-01 08:23','yyyy-mm-dd hh:mi'),'mi') from dual
----------------------------------------------------------------------
2008-3-1 8:23:00
2.确定执行时间间隔
1)、 每分钟执行
Interval => TRUNC(sysdate,'mi') + 1 / (24*60)
2)、 每天定时执行
例如:每天的凌晨2点执行
Interval => TRUNC(sysdate) + 1 +2 / (24)
3)、 每周定时执行
例如:每周一凌晨2点执行
Interval => TRUNC(next_day(sysdate,2))+2/24 --星期一,一周的第二天
4)、 每月定时执行
例如:每月1日凌晨2点执行
Interval =>TRUNC(LAST_DAY(SYSDATE))+1+2/24
5)、 每季度定时执行
例如每季度的第一天凌晨2点执行
Interval => TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 2/24
6)、 每半年定时执行
例如:每年7月1日和1月1日凌晨2点
Interval => ADD_MONTHS(trunc(sysdate,'yyyy'),6)+2/24
7)、 每年定时执行
例如:每年1月1日凌晨2点执行
Interval =>ADD_MONTHS(trunc(sysdate,'yyyy'),12)+2/24
- dbms_job.submit( job out binary_integer,
- what in archar2,
- next_date in date,
- interval in varchar2,
- no_parse in boolean)
其中:
●job:输出变量,是此任务在任务队列中的编号;
●what:执行的任务的名称及其输入参数;
●next_date:任务执行的时间;
●interval:任务执行的时间间隔。
其中Interval这个值是决定Job何时,被重新执行的关键;当interval设置为null时,该job执行结束后,就被从队列中删除。假如我们需要该job周期性地执行,则要用‘sysdate+m’表示。如何更好地确定执行时间的间隔需要我们掌握一个函数TRUNC。
1.TRUNC(for dates)
TRUNC函数为指定元素而截去的日期值。
其具体的语法格式如下:
TRUNC(date[,fmt])
其中:
date 一个日期值
fmt 日期格式,该日期将由指定的元素格式所截去。忽略它则由最近的日期截去
下面是该函数的使用情况:
1)按年截尾
select TRUNC(TO_DATE('2008-03-01 08:23','yyyy-mm-dd hh:mi'),'yyyy') from dual
-----------------------------------------------------------
2008-1-1
2)按月截尾
select TRUNC(TO_DATE('2008-03-01 08:23','yyyy-mm-dd hh:mi'),'mm') from dual
--------------------------------------------------------
2008-3-1
3)按日截尾
select TRUNC(TO_DATE('2008-03-01 08:23','yyyy-mm-dd hh:mi'),'dd') from dual
----------------------------------------------------------------------
2008-3-1
4)按时截尾
select TRUNC(TO_DATE('2008-03-01 08:23','yyyy-mm-dd hh:mi'),'hh') from dual
----------------------------------------------------------------------
2008-3-1 8:00:00
5)按分截尾
select TRUNC(TO_DATE('2008-03-01 08:23','yyyy-mm-dd hh:mi'),'mi') from dual
----------------------------------------------------------------------
2008-3-1 8:23:00
2.确定执行时间间隔
1)、 每分钟执行
Interval => TRUNC(sysdate,'mi') + 1 / (24*60)
2)、 每天定时执行
例如:每天的凌晨2点执行
Interval => TRUNC(sysdate) + 1 +2 / (24)
3)、 每周定时执行
例如:每周一凌晨2点执行
Interval => TRUNC(next_day(sysdate,2))+2/24 --星期一,一周的第二天
4)、 每月定时执行
例如:每月1日凌晨2点执行
Interval =>TRUNC(LAST_DAY(SYSDATE))+1+2/24
5)、 每季度定时执行
例如每季度的第一天凌晨2点执行
Interval => TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 2/24
6)、 每半年定时执行
例如:每年7月1日和1月1日凌晨2点
Interval => ADD_MONTHS(trunc(sysdate,'yyyy'),6)+2/24
7)、 每年定时执行
例如:每年1月1日凌晨2点执行
Interval =>ADD_MONTHS(trunc(sysdate,'yyyy'),12)+2/24
这里提供了一个简单的例子,主要是完成在每一个时间间隔内向一个表中插入一条记录
1)创建测试表
- SQL> create table test(id number,cur_time date);
- 表已创建。
- ----建sequence
- CREATE SEQUENCE test_sequence
- INCREMENT BY 1 -- 每次加几个
- START WITH 1 -- 从1开始计数
- NOMAXVALUE -- 不设置最大值
- NOCYCLE -- 一直累加,不循环
- CACHE 10 ;
--建触发器代码为:
- create or replace trigger tri_test_id
- before insert on test --test 是表名
- for each row
- declare
- nextid number;
- begin
- IF :new.id IS NULLor :new.id=0 THEN --id是列名
- select test_sequence.nextval --SEQ_ID正是刚才创建的
- into nextid
- from sys.dual;
- :new.id:=nextid;
- end if;
- end tri_test_id;
2)创建一个自定义过程
- SQL> create or replace procedure proc_test as
- 2 begin
- 3 insert into test(cur_time) values(sysdate);
- 4 end;
- 5 /
过程已创建。
3)创建JOB
- SQL> declare job1 number;
- begin
- dbms_job.submit(job1,'proc_test;',sysdate,'sysdate+1/1440');--每天1440分钟,即一分钟运行test过程一次
- end;
三、常用命令
JOB一些常用的命令
---停止job 25是建立的job test_job_really
begin
dbms_job.broken(25,true);
commit;
end;
/
--启动job
begin
dbms_job.run(25);
commit;
end;
/
--删除job
begin
dbms_job.remove(25);
commit;
end;
/
--查看执行结果
select * from test_job order by test_job.para_date desc;
--查看job
select * from sys.user_jobs
--使用下面的SQL查询是否JOB还在Running,前提是需要job执行时间不能过短
select * from dba_jobs_running
除了submit参数外,其余的几个参数有:
dbms_job.run(v_job); //运行job
dbms_job.broken(v_job,true,next_date); //停止一个job,里面参数true也可是false,next_date(某一时刻停止)也可是sysdate(立刻停止)。
dbms_job.remove(v_job); //删除某个job
dbms_job.what(v_job,'sp_fact_charge_code;'); //修改某个job名
dbms_job.next_date(v_job,sysdate); 修改下一次运行时间
如果JOB不能自动运行,需要设置一些参数,参数如下:
利用Oracle的Job Queue实现定时操作
1、确保Oracle的工作模式允许启动队列管理器(SNP进程)
SVRMGRL> Alter System Enable Restricted Session;
或 SQL> Alter System Disenable Restricted Session;
2、确保Oracle已配置任务队列管理器的启动参数
initSID.ora中:
job_queue_process=n(0);(0-36)个进程
job_queue_interval=N(60);(1-3600)秒唤醒
3、将任务加入数据库的任务队列中
DBMS_JOB.Submit (
job out binary_integer, (任务号)
what in varchar2, (执行任务的名称及入参)
next_date in date, (任务执行的时间)
interval in varchar2, (任务执行的间隔。null:执行一次;sysdate+m:周期性,每隔m天执行)
no_parse in boolean)
同时
grant execute on dbms_job to ...;
4、将要执行的任务,为存储过程等PL/SQL程序段
SQL> variable n number;
SQL> begin
2 dbms_job.submit(n, my_job, sysdate, sysdate + 1/360);
3 commit;
4 end
5 /
SQL> print :n;
5、查看Job
Table :user_jobs、dba_jobs
Column:job (任务号)
next_date (下次执行日期)
next_sec (下次执行时间)
failures (失败次数,失败16次则自动终止)
broken (终止标志,Y/N)
在数据库找到initsid.ini这个配置文件,进行这些选项的配置,
SQL> show parameters job
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 0
数据库配置只有这项,值是0,说明没有设置
修改可执行作业个数为20个
ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 20;
这样就可以自动运行JOBL 。
6、一些必要的参数
修改initsid.ora参数
job_queue_processes = 4
job_queue_interval = 10
job_queue_keep_connections=true
修改可执行作业个数为20个
ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 20
修改取消限制模式
ALTER SYSTEM DISABLE RESTRICTED SESSION;
7、两个必要的表
user_jobs及dba_jobs_running