oracle创建计划任务

特别提示：
oracle是执行完任务，才按照interval去计算下次执行时间！！！

为精确每个5分钟执行一个任务，必须自己计算时间。
如:trunc_minute(sysdate)+5/1440

create or replace function trunc_minute(v_date date) return date as

begin
      return to_number(trunc(to_char(v_date, 'mi')/5))*5/(24*60) + trunc(v_date, 'hh24');
end;

创建存储过程

备注：默认在存储过程中是不允许执行truncate table tablename，所以要加

execute   immediate 
create or replace procedure truncate_table as
begin
       execute   immediate 'truncate table test_STATUS';
end;

每天3点执行

在pl/sql

declare  
  jobno number;
begin
dbms_job.submit(job =>jobno,
                    what =>'truncate__table;',
                    next_date =>sysdate,
                    interval =>'trunc(sysdate + 1) + 3/24');
end;

sql plus

variable jobno number;
begin
sys.dbms_job.submit(job => :jobno,
what => 'change_date;',
next_date => to_date('18-11-2013', 'dd-mm-yyyy'),
interval => 'sysdate+1/1440');--每天1440分钟，即一分钟运行change_date过程一次
commit; --必须有commit，如果没有是查不到该job的！！！
end;

variable相当于一个sql*plus环境的全局变量,declare里定义的是pl/sql中的局部变量。

查看job

select * from user_jobs;--查看当前用户的调度任务
select * from dba_jobs_running;--查看正在执行的调度任务
select * from dba_jobs;--查看执行完的调度任务
select * from all_jobs; -- 查看数据库系统所有任务

常用时间设置

（1）如果想每天凌晨1 点执行，则此参数可设置为
'trunc(sysdate)+25/24'；
（2）如果想每周一凌晨1 点执行，则此参数可设置为
'trunc(next_day(sysdate,1))+25/24'；
（3）如果想每月1 号凌晨1 点执行，则此参数可设置为
'trunc(last_day(sysdate))+25/24'；
（4）如果想每季度执行一次，则此参数可设置为
'trunc(add_months(sysdate,3),'Q')+1/24'；
（5）如果想每半年执行一次，则此参数可设置为'add_months(trunc(sysdate,'yyyy'),6)+1/24'；
（6）如果想每年执行一次，则此参数可设置为'add_months(trunc(sysdate,'yyyy'),12)+1/24'。

每天运行一次
'SYSDATE + 1'
每小时运行一次
'SYSDATE + 1/24'
每10分钟运行一次
'SYSDATE + 10/（60*24）'
每30秒运行一次
'SYSDATE + 30/(60*24*60)'
每隔一星期运行一次
'SYSDATE + 7'
每分钟
dbms_job.submit(:v_job_no, 'insert into job_test values(sysdate);', sysdate,
'sysdate+1/1440');

备注：

oracle定时执行job queue 的后台进程是SNP,要启动

snp,首先看系统模式是否支持

sql> alter system enable restricted session;
或
sql> alter system disenable restricted session;

利用上面的命令更改系统的会话方式为disenable restricted,为snp的启动创建条件.


再有就是配置job queue的启动参数,snp的启动参数位于oracle的初始化文件中,
job_queue_processes=10   (oracle10gde 默认值)
job_queue_interval=N

第一行定义snp进程的启动个数为10,正常范围是0-36,根据任务的多少,可以配置
不同的数值.

第二行定义系统每隔几秒唤醒该进程一次.缺省是60,正常范围是1-3600秒.事实上，该进程执行完

当前任务后，就进入睡眠状态，睡眠一段时间后，由系统的总控负责将其唤醒。  
如果该文件中没有上面两行，请按照如上配置添加。配置完成后，需要重新启动数据库，使其生效

。注意：如果任务要求执行的间隔很短的话，N的配置也要相应地小一点。


查看job queue的详细信息,查询数据库字典 user_jobs

eg:
 sql> select job,next_date,next_sec,broken from user_jobs;

实验测试：

一、在PLSQL中创建表：

  1. create table HWQY.TEST   
  2.   
  3. 　　(   
  4.   
  5. 　　CARNO     VARCHAR2(30),   
  6.   
  7. 　　CARINFOID NUMBER   
  8.   
  9. 　　)  
　　

   二、在PLSQL中创建存储过程：

  1. create or replace procedure pro_test   
  2.   
  3. 　　AS   
  4.   
  5. 　　carinfo_id number;   
  6.   
  7. 　　BEGIN   
  8.   
  9. 　　select s_CarInfoID.nextval into carinfo_id   
  10.   
  11. 　　from dual;   
  12.   
  13. 　　insert into test(test.carno,test.carinfoid) values(carinfo_id,'123');   
  14.   
  15. 　　commit;   
  16.   
  17. 　　end pro_test;  
 

     三、在SQL命令窗口中启动任务：

在SQL>后执行

  1. VARIABLE jobno number;   
  2.   
  3. 　　begin   
  4.   
  5. 　　DBMS_JOB.SUBMIT(:jobno,   
  6.   
  7. 　　'pro_test;',   
  8.   
  9. 　　SYSDATE,'sysdate+1/24/12');   
  10.   
  11. 　　commit;   
  12.   
  13. 　　end;  
 

　  四、跟踪任务的情况(查看任务队列)：

  1. SQL>  select job,next_date,next_sec,failures,broken from user_jobs;   
  2.   
  3. 　　JOB NEXT_DATE   NEXT_SEC           FAILURES BROKEN   
  4.   
  5. 　　---------- ----------- ---------------- ---------- ------   
  6.   
  7. 　　1 2008-2-22 ?01:00:00                  0 N  
 

说明有一个任务存在了。

执行select * from test t查看定时任务的结果。可以看出定时任务是正常执行了的。

    

    五、停止已经启动的定时任务:

先执行select job,next_date,next_sec,failures,broken from user_jobs;

以查看定时任务的job号。

在SQL>中执行下面的语句停止一个已经启动的定时任务：

  1. begin   
  2.   
  3. 　　dbms_job.remove(1);   
  4.   
  5. 　　commit;   
  6.   
  7. 　　end;  
 

表示停止job为1的任务。

执行后显示如下：

 
  1. PL/SQL procedure successfully completed  
 

　六、查看进程数：

  1. show parameter job_queue_processes;  
 

必须>0， 否则执行下面的命令修改：

  1. alter system set job_queue_processes=5;  
 

七、再创建一个任务(每5分钟执行一次)：

  1. variable jobno number;   
  2.   
  3. 　　begin   
  4.   
  5. 　　dbms_job.submit(:jobno, 'pro_test;',   
  6.   
  7. 　　sysdate,'sysdate+1/24/12');   
  8.   
  9. 　　commit;   
  10.    
  11. 　　end;  
 
 

建立一个定时任务后，在PLSQL中查看JOB，它的sql语句类似的是如下：

  1. begin   
  2.   
  3. 　　sys.dbms_job.submit(job => :jobno,   
  4.   
  5. 　　what => 'pro_test;',   
  6.   
  7. 　　next_date => to_date('21-02-2008 17:37:26', 'dd-mm-yyyy hh24:mi:ss'),   
  8.   
  9. 　　interval => 'sysdate+1/24/12');   
  10.   
  11. 　　commit;   
  12.   
  13. 　　end;  
 

所以，创建一个任务的完整的格式是：

  1. variable jobno number;   
  2.   
  3. 　　begin   
  4.   
  5. 　　sys.dbms_job.submit(job => :jobno,   
  6.   
  7. 　　what => 'pro_test;',   
  8.   
  9. 　　next_date => to_date('21-02-2008 17:37:26', 'dd-mm-yyyy hh24:mi:ss'),   
  10.   
  11. 　　interval => 'sysdate+1/24/12');   
  12.   
  13. 　　commit;   
  14.   
  15. 　　end;

 

--系统会自动分配一个任务号jobno。

　　八、 执行作业查看

  1. select job,next_date,next_sec,failures,broken from user_jobs;  
 

结果：

JOB NEXT_DATE NEXT_SEC FAILURES BROKEN

1 1 2008-2-22 AM 01:00:00 01:00:00 0 N

2 2 2008-2-21 PM 05:42:45 17:42:45 0 N

3 3 2008-2-21 PM 05:42:45 17:42:45 0 N

       

 九、创建 job的格式：  

  1. 　    
  2. 　　DBMS_JOB.SUBMIT(:jobno,//job号     
  3.   
  4. 　　'your_procedure;',//要执行的过程     
  5.   
  6. 　　trunc(sysdate)+1/24,//下次执行时间     
  7.   
  8. 　　'trunc(sysdate)+1/24+1'//每次间隔时间     
  9.   
  10. 　　);  
 

删除job:dbms_job.remove(jobno);

修改要执行的操作:job:dbms_job.what(jobno,what);

修改下次执行时间：dbms_job.next_date(job,next_date);

修改间隔时间：dbms_job.interval(job,interval);

停止job:dbms.broken(job,broken,nextdate);

启动job:dbms_job.run(jobno);

例子：

  1. VARIABLE   job35   number;     
  2.   
  3. 　　begin     
  4.   
  5. 　　DBMS_JOB.SUBMIT(job35 ,       
  6.   
  7. 　　'Procdemo;',//Procdemo为过程名称       
  8.   
  9. 　　SYSDATE,   'SYSDATE   +   1/720');     
  10.   
  11. 　　commit;     
  12.   
  13. 　　end;     
  14.   
  15. 　　  
  
创建JOB
variable jobno number;
begin
dbms_job.submit(:jobno, 'P_CRED_PLAN;',SYSDATE,'SYSDATE+1/2880',TRUE);
commit;
-- 查看Job信息

SELECT * FROM DBA_JOBS ;

SELECT * FROM DBA_JOBS_RUNNING;

SELECT * FROM g_test ORDER BY ID ASC;
 

运行JOB
SQL> begin
dbms_job.run(:job1);
end;
/

 

删除JOB
SQL> begin
dbms_job.remove(:job1);
end;

　十、执行作业必须设置的参数

-----修改job_queue_processes的值（保证其不为0否则JOB不自动运行)

可通过select   *   from   v$parameter;查看其值；       

 方法1、startup   pfile='C:oracleora90databaseinitorcl.ora';

需要修改initorcl.ora文件的job_queue_processes参数，然后重新启动数据库以后才能生效       

 方法2、alter   system   set   job_queue_processes=10

不需要重新启动数据库就能生效，系统自动修改init.ora文件

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/wuxl360/p/5455275.html