ORCLE10g 数据库定时任务

 

如果想让oracle 数据库定时执行一个什么任务，可以用如下的方法实现：
上例子：

准备东西：

create table t3(k integer primary key,  d date);   -- 例子表

create sequence seq_test start with 1 increment by 1;   --例子序列

create or replace procedure insert_into_t3 as  --例子过程
begin
  insert into t3 (k, d) values (seq_test.nextval, sysdate); --把t1里面的相关数据插到t2中
  commit; --插入一条记录commit一次
end;

下面的代码，将每分钟执行一下上面的insert_into_t3过程

var job number;

begin
   dbms_job.submit(
     job => :job,
     what => 'insert_into_t3;',
     next_date => to_date('2014-06-17 12:35:00','yyyy-MM-dd hh24:mi:ss'),
     interval => 'sysdate+1/(24*60)'
  );
  commit ;
end;

可以通过下面的语句查询一下任务列表：

SELECT * from user_jobs order by next_date;

如果想手动触发一下任务，可以用如下语句：

begin
      dbms_job.run(jobNum);
end;
/

通过下面的语句删除任务

begin
      dbms_job.remove(jobNum);
end;
/

注意：有一个系统参数需要特别注意：job_queue_processes
如果 show parameter job_queue_processes 显示其值为0,则定时任务不会执行。
需要用如下语句给它设置一个>0的值。例如：
alter system set job_queue_processes=10 ; 

评论

1 楼 jieyanqulaopo123 2014-11-19  

如果定时任务不能运行，可以根据如下列表进行检查原因：
Checklist for job issues

For convenience, I've included the brief checklist here:

Is the Database Instance in RESTRICTED SESSIONS mode?
Is the parameter JOB_QUEUE_PROCESSES set to 0?
Is the hidden parameter '_SYSTEM_TRIG_ENABLED' set to FALSE?
Is the job marked 'BROKEN' in the DBA_JOBS table?
Have you issued a commit after submitting the job?
Have you tried setting the hidden parameter, "_job_queue_interval" to a different value (default is 5 seconds)?
Has the database server (machine) been up for more than 497 days
Check Internal Oracle bug: 3427424 "SLGCSF / SLGCS STOP INCREMENTING AFTER 497 DAYS UPTIME"
It is fixed in 10.1.0.2.0. - workaround is to reboot of the database server (see also Solution for the 497 day bug below).
Is the job still running in DBA_JOBS_RUNNING?
Does the LAST_DATE and NEXT_DATE fields in DBA_JOBS make sense for the particular job?
If LAST_DATE is null, the job has never executed automatically.
Does the NEXT_DATE field change per the INTERVAL field in DBA_JOBS?
If not, it's not automatically working?
Have you tried changing the value for JOB_QUEUE_PROCESSES to '0' and then back again?
This restarts the CJQ process.
See Oracle Bug 2649244 (fixed by: 9015, 9203, 10201)
Finally, if you have upgraded or refreshed the database, try executing 'exec dbms_ijob.set_enabled(true);' as sysadm.

-- http://www.confio.com/logicalread/why-wont-my-oracle-jobs-run/#.U5_Hv0AvilY