Oracle 定时任务job实际应用

十念  金牌会员 | 2023-5-11 16:18:03 | 显示全部楼层 | 阅读模式
打印 上一主题 下一主题

主题 923|帖子 923|积分 2769

目录

一、Oracle定时任务简介

Oracle定时任务是在oracle系统中一个非常重要的子系统,运用得当,可以大大提高我们系统运行和维护能力。oracle定时任务的功能,可以在指定的时间点自行执行任务。
那么在实际工作中,什么样的场景会用到定时任务呢?下面是在实际工作中用到的真实业务场景举例

  • 在生成环境中,有时候需要在表中记录一些业务日志,系统运行时间长了之后,表中日志会越来越多,导致系统性能下降,这时候就需要用到定时任务,定时去删除表中一些时间年代比较久远垃圾数据。
  • 在某些业务场景中,明细表的数据量特别大,而需要查询明细表一下汇总数据,就需要将明细表中的数据【某天的业务产生的金额、人数等】通过计算汇总到另外表中,这样在查询的时候就能优化查询效率。而以上操作需要在业务量比较少的情况下进行【一般都在凌晨之后】,这是就需要用到定时任务。
二、dbms_job涉及到的知识点


  • 创建job脚本
  1. variable jobno number;
  2. dbms_job.submit(:jobno,       —-job号
  3. 'your_procedure;',            —-执行的存储过程, ';'不能省略
  4. next_date,                    —-下次执行时间
  5. 'interval'                    —-每次间隔时间,
  6. interval                      以天为单位);
复制代码
上面是通过脚本创建,当然也可以通过plsql图形化工具来创建,具体创建过程如下


**系统会自动分配一个任务号jobno ** ,根据jobno 可以进行如下定时任务操作

  • 删除job: dbms_job.remove(jobno);
  • 修改要执行的操作: job:dbms_job.what(jobno, what);
  • 修改下次执行时间:dbms_job.next_date(jobno, next_date);
  • 修改间隔时间:dbms_job.interval(jobno, interval);
  • 启动job: dbms_job.run(jobno);
  • 停止job: dbms.broken(jobno, broken, nextdate); –broken为boolean值
三、初始化相关参数job_queue_processes


  • job_queue_process表示oracle能够并发的job的数量,当job_queue_process值为0时表示全部停止oracle的job
  • 查看job_queue_processes参数
  1. show parameter job_queue_process;
  2. 或者
  3. select * from v$parameter where name='job_queue_processes';
复制代码

  • 修改job_queue_processes参数
  1. alter system set job_queue_processes = 10;
复制代码
四、实际创建一个定时任务(一分钟执行一次),实现定时一分钟往表中插入数据

4.1 创建需要定时插入数据的目标表
  1. create table t_test (id varchar2(30),
  2.                   name varchar2(30)
  3.                  );
复制代码
4.2 创建定时执行的存储过程
  1. create or replace procedure proce_t is
  2. begin
  3.   insert into t_test
  4.     (id, name)
  5.   values
  6.     ('1', to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'));
  7.   commit;
  8. end proce_t;
  9. /
复制代码
4.3 创建定时一分钟定时任务job
  1. variable jobno number;
  2. begin
  3. dbms_job.submit(:jobno,
  4. 'proce_t;',
  5. sysdate,
  6. 'sysdate+1/24/60');
  7. commit;
  8. end;
复制代码
4.5 可以根据以下语句查询刚创建好的job
  1. select job, next_date, next_sec, failures, broken from user_jobs where job = '1424'
复制代码
查询结果如下
  1. SQL> select job, next_date, next_sec, failures, broken from user_jobs where job = '1424'
  2.   2  /
  3.        JOB NEXT_DATE   NEXT_SEC           FAILURES BROKEN
  4. ---------- ----------- ---------------- ---------- ------
  5.       1424 2020-12-30  13:07:14                  0 N
复制代码
其中broken = N 表示该job已经生效
我们再来查看目标表中有没有定时插入数据
  1. SQL> select * from t_test;
  2. ID                             NAME
  3. ------------------------------ ------------------------------
  4. 1                              2020-12-30 13:05:14
  5. 1                              2020-12-30 13:03:14
  6. 1                              2020-12-30 13:04:14
  7. 1                              2020-12-30 13:08:14
  8. 1                              2020-12-30 13:06:14
  9. 1                              2020-12-30 13:07:14
  10. 6 rows selected
复制代码
可以看到,定时一分钟插入了一条数据。
4.6 如果我们不需要这个定时任务了,那要怎么停止呢?

1、根据jobno,执行以下脚本可以停止job
  1. SQL> begin
  2.   2    dbms_job.broken(1424, true, sysdate);
  3.   3    commit;
  4.   4  end;
  5.   5  /
  6. PL/SQL procedure successfully completed
复制代码
再来查看定时任务是否停用成功
  1. SQL> select job, next_date, next_sec, failures, broken from user_jobs where job = '1424';
  2.        JOB NEXT_DATE   NEXT_SEC           FAILURES BROKEN
  3. ---------- ----------- ---------------- ---------- ------
  4.       1424 4000-01-01  00:00:00                  0 Y
复制代码
我们发现BROKEN=Y   说明定时任务已经停止成功了
4.7 启用刚才停用的定时任务
  1. SQL> begin
  2.   2   dbms_job.run(1424);
  3.   3   commit;
  4.   4  end;
  5.   5  /
  6. PL/SQL procedure successfully completed
  7. SQL> select job, next_date, next_sec, failures, broken from user_jobs where job = '1424';
  8.        JOB NEXT_DATE   NEXT_SEC           FAILURES BROKEN
  9. ---------- ----------- ---------------- ---------- ------
  10.       1424 2020-12-30  13:20:53                  0 N
复制代码
BROKEN = N ,刚才的定时任务又启动了
五、定时任务中job运行时间

下面总计了一些定时任务中常用的运行时间

  • 每分钟执行: TRUNC(sysdate,'mi') + 1/(24*60)
  • 半个小时: sysdate+30/(24*60)
  • 每天凌晨1点执行:TRUNC(sysdate) + 1 +1/(24)
  • 每周一凌晨1点执行: TRUNC(next_day(sysdate,'星期一'))+1/24
  • 每月1日凌晨1点执行: TRUNC(LAST_DAY(SYSDATE))+1+1/24
  • 每季度的第一天凌晨1点执行: TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 1/24
  • 每年7月1日和1月1日凌晨1点: ADD_MONTHS(trunc(sysdate,'yyyy'),6)+1/24
  • 每年1月1日凌晨1点执行: ADD_MONTHS(trunc(sysdate,'yyyy'), 12)+1/24
  • 每个小时的第15分钟运行,比如:8:15,9:15,10:15…:trunc(sysdate,'hh')+(60+15)/(24*60)

免责声明:如果侵犯了您的权益,请联系站长,我们会及时删除侵权内容,谢谢合作!

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有账号?立即注册

x
回复

使用道具 举报

0 个回复

倒序浏览

快速回复

您需要登录后才可以回帖 登录 or 立即注册

本版积分规则

十念

金牌会员
这个人很懒什么都没写!

标签云

快速回复 返回顶部 返回列表