Oracle 定时任务作业调度计划 DBMS_JOB DBMS_SCHEDULE使用方法

向右看齐 2022-04-11 13:48 1649阅读 0赞

# oracle定时任务dbms\_job与dbms\_scheduler使用方法 #

2018年08月20日 13:18:20 [风灵使][Link 1] 阅读数：111

工作中需要一个定时任务来抽取数据，之前采用的是`dbms_job`包下的过程来创建`job`,遇到了一些问题。找了下资料，得知`oracle10g`以后就推荐采用`dbms_scheduler`包来取代`dbms_job`来创建定时任务。下面简单介绍下两者的使用方法及使用过程中的一些体会。

1.先创建日志表，用于记录存储过程执行时间及结果

create table bak_job_test(date_time date,mark varchar2(200));

*  1

2.创建一个存储过程，用于创建表

create or replace procedure my_test authid current_user is
           v_count number := 0;
           v_mess varchar2(200) := '';
         begin
          select count(1) into v_count from user_tables t where t.TABLE_NAME = 'BAK_JOB_TABLES';
           if  v_count > 0 then
              execute immediate 'drop table bak_job_tables purge';
           end if;
          execute immediate 'create table bak_job_tables as select * from user_tables where 1=2';
          insert into bak_job_test(date_time,mark) values(sysdate,'success');
    exception
    when others then
    v_mess := substr(SQLERRM,0,200);
    insert into bak_job_test(date_time,mark) values(sysdate,v_mess);
          end;

*  1
 *  2
 *  3
 *  4
 *  5
 *  6
 *  7
 *  8
 *  9
 *  10
 *  11
 *  12
 *  13
 *  14
 *  15

3.使用`dbms_job`包创建定时任务

declare
              myjob number; 
           begin 
    dbms_job.submit(myjob,'begin my_test; end;',sysdate,'TRUNC(sysdate,''mi'') + 1 / (24*60)');
    commit;
            end;

*  1
 *  2
 *  3
 *  4
 *  5
 *  6

定时器1分钟执行一次，调用存储过程创建表，结果报错：权限不足。之前了解过，定义存储过程时加上`authid current_user`就可以在存储过程里面使用当前用户所角色的权限，出现这种问题 让人很费解，手动授权`grant create table to user`之后，确实可以解决这个问题，但是这种方式不通用，特别是存储过程里面用到其他的权限的时候就不方便了。所以决定试试`dbms_scheduler`包来创建定时任务。

先简单介绍下`dbms_job`包下常见的过程：

1) `dbms_job.remove(jobId)` 删除`job`定时任务，可以从`user_jobs`视图中查看`job`的详细情况

2) `dbms_job.run(jobid)` 运行定时任务

3) `dbms_job.broken(jobid,true)` 终止定时任务

4) `dbms_job.interval(jobid,'interval')` 修改定时任务的执行时间

4.使用`dbms_scheduler`创建定时任务

使用`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;

*  1
 *  2
 *  3
 *  4
 *  5
 *  6
 *  7
 *  8
 *  9

定时器执行，调用存储过程创建表成功了，不需要显示的授权`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`，即使其他对象依赖于它，操作也能成功