Oracle定时任务

一、描述

常要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使用方法：

 

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;

  PL/SQL   JOB已成功完成。 

 

 

 

三、常用命令

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