开发者社区> 烟火_> 正文

oracle定时任务

简介: 一、简介   当我们需要oracle数据库定时自动执行一些脚本,或进行数据库备份、数据库的性能优化,包括重建索引等工作是需要使用到定时任务。   定时任务可以使用以下两种完成。   1、操作系统级的定时任务,win的定时任务,unix的crontab   2、数据库级的定时任务。
+关注继续查看

一、简介
  当我们需要oracle数据库定时自动执行一些脚本,或进行数据库备份、数据库的性能优化,包括重建索引等工作是需要使用到定时任务。
  定时任务可以使用以下两种完成。
  1、操作系统级的定时任务,win的定时任务,unix的crontab
  2、数据库级的定时任务。
  相对来说,数据库的定时任务效率会更高。
  oracle的定时任务:使用oracle定时任务时,需要使用到oracle中dbms_job.submit函数。

DBMS_JOB过程的公共参数

Job BINARY_INTEGER 任务的唯一识别号
What VARCHAR2 作为任务执行的PL/SQL代码
Next_date VARCHAR2 任务下一次运行的时间
Interval VARCHAR2 日期表达式,用来计算下一次任务运行的时间

1、job参数
  job是一个整数,用来唯一标示一个任务。该参数既可由用户指定也可由系统自动赋值,这取决于提交任务时选用了哪一个任务提交过程。DBMS_JOB.SUBMIT过程通过获得序列SYS.JOBSEQ的下一个值来自动赋予一个任务号。该任务号是作为一个OUT参数返回的,所以调用者可以识别出提交的任务。而DBMS_JOB.ISUBMIT过程则由调用者给任务指定一个识别号,此时,任务号的唯一性就完全取决于调用者。 除了删除或者重新提交任务,一般来说任务号是不能改变的。即使当数据库被导出或者被导入这样的情况,任务号也将被保留下来。所以在执行含有任务的数据的导入/导出操作时很可能会发生任务号冲突的现象。

2、what
  what参数是一个可以转化为合法PL/SQL调用的字符串,该调用将被任务队列自动执行。在what参数中,如果使用文字字符串,则该字符串必须用单引号引起来。 what参数也可以使用包含我们所需要字符串值的VARCHAR2变量。实际的PL/SQL调用必须用分号隔开。在PL/SQL调用中如果要嵌入文字字符串,则必须使用两个单引号。 what参数的长度在Oracle7.3中限制在2000个字节以内,在Oracle 8.0以后,扩大到了4000个字节,这对于一般的应用已完全足够。该参数的值一般情况下都是对一个PL/SQL存储过程的调用。实际经验就是最好将存储过程调用封装在一个匿名块中,这样可以避免一些错误的产生。如,一般情况下,what参数可以这样引用: what =>’my_procedure(parameter1);’ 但是比较安全的引用,应该这样写:what =>’begin my_procedure(parameter1); end;’。任何时候,我们只要通过更改what参数就可以达到更改任务定义的目的。但是有一点需要注意,通过改变what参数来改变任务定义时,任务的运行时间将还是和之前的一样。需要重新设置新的运行时间,从而达到定时任务的目的。

3、next_date
  next_date参数是用来调度任务队列中该任务下一次运行的时间。这个参数对于DBMS_JOB.SUBMIT和DBMS_JOB.BROKEN这两个过程默认为系统当前时间,也就是说任务将立即运行。当将一个任务的next_date参数赋值为null时,则该任务下一次运行的时间将被指定为4000年1月1日,也就是说该任务将永远不再运行。但是,如果想在任务队列中保留该任务而又不想让其运行,可以将next_date设置为null。Next_date也可以设置为过去的一个时间。系统任务的执行顺序是根据它们下一次的执行时间来确定的,所以将next_date参数设置回去就可以达到将该任务排在任务队列前面的目的。这在当任务队列进程不能跟上将要执行的任务或者一个特定的任务需要尽快执行的时候可以这样设置。

4、Interval
  interval 参数 是一个表示Oracle合法日期表达式的字符串。这个日期字符串的值在每次任务被执行时算出,算出的日期表达式有两种可能,要么是未来的一个时间要么就是null。next_date是在一个任务开始时算出的,而不是在任务成功完成时算出的。 当任务成功完成时,系统通过更新任务队列目录表将前面算出的next_date值置为下一次任务要运行的时间。当由interval表达式算出next_date是null时,任务自动从任务队列中移出,不会再继续执行。因此,如果传递一个null值给interval参数,则该任务仅仅执行一次。 通过给interval参数赋各种不同的值,可以设计出复杂运行时间计划的任务。

二、例子

   存储过程更多情况是在数据库方作数据整合等复杂的工作.比如银行系统,数据相当重要,每天需要备份重要表的数据。每天的数据有十多万,此时需要用到定时任务进行备份。

一般思路如下:
1.每天备份完数据之后,max(id)存到一张表
2.下次备份的时候select max(id);
3.select * from table where id > max(id);

  示例代码:

每分钟备份一次person表中增加的记录

person表结构如下:

CREATE TABLE person(
id NUMBER(11) NOT NULL ,
username VARCHAR2(255 ) NULL ,
age NUMBER(11) NULL ,
password VARCHAR2(255) NULL ,
PRIMARY KEY (id)
)

备份表person_back表结构如下:

CREATE TABLE person_back (
id NUMBER(11) NOT NULL ,
username VARCHAR2(255 ) NULL ,
age NUMBER(11) NULL ,
password VARCHAR2(255) NULL ,
PRIMARY KEY (id)
)

辅助表tb_maxid,用于存放当前person表中最大的id

create table tb_maxid(id number);
insert into tb_maxid values(0);

其他代码如下:

/*
  1、提取person,所有数据
  2、循环插入到person_back表中
  3、完成之后记录max(id)到tb_maxid表
  
  主要问题:
  1、如果在循环中commit,效率会很低
  2、假设数据很大(千万)回滚段不够(抛异常)。回滚段的默认大小可以设置
       2.1、加大回滚段。
       2.2分段提交
*/
-- 存储过程,保存person记录
create or replace procedure pro_back_person(
       v_maxid number
)
is
   -- 定义游标
   cursor c_person is
   select * from person where id > v_maxid;
   --定义rowtype
   r_person person%rowtype;
   --判断循环次数
   v_index number := 0;
   -- 
   v_max number;
begin
  -- 判断游标是否打开
  if c_person%isopen then --游标打开
    null;
  else
   open c_person;
  end if;
  --循环备份数据
  loop
    fetch c_person into r_person;
    exit when c_person%notfound;
    
    insert into person_back values(r_person.id,r_person.username, r_person.age, r_person.password);
  
    --分段提交
    v_index := v_index + 1;   
    if(v_index = 2000) then
       commit;
       v_index := 0;
    end if;
    
  end loop;
  commit; --再次提交数据
  
  select max(id) into v_max from person_back;
  update tb_maxid set id = v_max;
  commit;
  --关闭游标
  close c_person;
end pro_back_person;
-- 调用保存记录的存储过程
create or replace procedure invokebackpro
is
   v_maxid number;
begin
  select id into v_maxid from tb_maxid;
  pro_back_person(v_maxid);
end invokebackpro;
-- 创建任务定时器
declare
    jobno number;
begin
  dbms_job.submit(
    jobno,
    what => 'invokebackpro;', --invokebackpro为存储过程的名称
    Interval => 'TRUNC(sysdate, ''mi'')+1/(24*60)' --定义事件间隔每分钟
  );
  commit;
end;

三、其他相关

 一些常见Interval设置如下:

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(ADD_MONTHS(sysdate)) + 1 + 2/24
5、没季度定时执行(没嫉妒的定义天凌晨2点执行)
    Interval => TRUNC(ADD_MONTHS(sysdate, 3), 'Q') + 2/24
6、每半年定时执行(每年7月1日和1月1日凌晨2点)
    Interval => TRUNC(ADD_MONTHS(sysdate, 'yyyy'), 6) + 2/24
7、每年定时执行(每年1月1日凌晨2点执行)    
    Interval => TRUNC(ADD_MONTHS(sysdate, 'yyyy'), 12) + 2/24

  oracle中关于定时器的数据字典如下:
  DBA_JOBS 本数据库中定义到任务队列中的任务
  DBA_JOBS_RUNNING 目前正在运行的任务
  USER_JOBS 当前用户拥有的任务

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
简单的定时任务
View Code using System; using System.Collections.Generic; using System.ComponentModel; using System.
553 0
Oracle11g select查询时候输出未选定行
解决方法是: 查询的表名是否是大写的; 是否没有提交执行结果:可以commit一下;
1242 0
Mysql编写定时任务事件
原文:Mysql编写定时任务事件 场景:   例如:某系统,用户每天只能拥有一次的抽奖机会,抽过后当天就不可再抽,但是过了24:00点后用户就重新拥有一次抽奖机会。像这种需要数据库定时对某个字段进行更新操作的任务,完全可以通过数据库本身的事件机制进行操作,而服务器端也不需要用额外的代码进行这种监控定时的操作。
1053 0
阿里云服务器怎么设置密码?怎么停机?怎么重启服务器?
如果在创建实例时没有设置密码,或者密码丢失,您可以在控制台上重新设置实例的登录密码。本文仅描述如何在 ECS 管理控制台上修改实例登录密码。
23561 0
oracle通过job定时执行任务
2013年8月27日 星期二 20:49 在项目中,经常会遇到需要定时完成的任务,比如定时更新数据,定义统计数据生成报表等等,其实这些事情都可以使用Oracle的Job来完成。下面考试大就结合我们实验室项目实际,简单介绍一下在Oracle数据库中通过Job完成自动创建表的方法。   整个过程总共分为两步。虽然整个过程都非常简单,但是对于初学Oracle的生手还是有很多地方需要注意的。
1740 0
【OUTLINE】使用Oracle Outline技术暂时锁定SQL的执行计划
  Oracle的Outline技术可以在特殊情况下保证执行计划的稳定性。在极端情况下可以使用此项技术实现暂时锁定执行计划的目的。  主要使用场景如下:  ①短时间内无法完成SQL的优化任务,此时可以使用outline暂时锁定SQL执行计划;  ②在CBO优化模式下,当统计信息出现问题时,会导致执行计划出现异常变化,此时可以使用outline暂时调整SQL执行计划;  ③由于数据库的bug导致SQL的执行计划出现异常,使用outline锁定执行计划。
971 0
ubuntu16定时任务crontab
最近在ubuntu16上配置了个定时任务,好长时间没配置好,后来发现问题,cron配置文件换行符必须是 unix格式,否则执行不成功
2627 0
SpringBoot定时任务
项目中很多时候会使用到定时任务,这篇文章介绍一下springboot整合定时任务。 springboot整合定时任务其实就两点, 1.创建一个能被定时任务类,方法上加入@Scheduled注解 2.
960 0
oracle job 定时执行 存储过程
oracle job 定时执行 存储过程   一:简单测试job的创建过程案例: 1,先创建一张JOB_TEST表,字段为a 日期格式 SQL> create table JOB_TEST(a date); Table created ...
922 0
+关注
烟火_
从事java开发多年
113
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
JS零基础入门教程(上册)
立即下载
性能优化方法论
立即下载
手把手学习日志服务SLS,云启实验室实战指南
立即下载