oracle job

VARIABLE jobno
number;BEGINDBMS_JOB.SUBMIT(:jobno,’p_test;’SYSDATE,’SYSDATE +
1/72’);commit;END;DBMS_JOB.SUBMIT(:jobno,//job号’your_procedure;’,//要执行的过程trunc(sysdate)+1/24,//下次执行时间’trunc(sysdate)+1/24+1’//每次间隔时间);

以下由sqlplus 来执行,具体参照一下相关的文档
VARIABLE jobno number;
BEGIN
DBMS_JOB.SUBMIT(:jobno,
’p_test;’
SYSDATE,’SYSDATE + 1/72’);
commit;
END;

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

oracle系统包——dbms_job用法(oracle定时任务)

用于安排和管理作业队列,通过使用作业,可以使ORACLE数据库定期执行特定的任务。

一、dbms_job涉及到的知识点
1、创建job:
variable jobno number;
dbms_job.submit(:jobno, —-job号  
                ‘your_procedure;’,—-执行的存储过程, ‘;’不能省略 
                next_date, —-下次执行时间  
                ‘interval’ —-每次间隔时间,interval以天为单位
); 
–系统会自动分配一个任务号jobno。
2、删除job: dbms_job.remove(jobno); 
3、修改要执行的操作: job:dbms_job.what(jobno, what);  
4、修改下次执行时间:dbms_job.next_date(jobno, next_date);  
5、修改间隔时间:dbms_job.interval(jobno, interval);
6、启动job: dbms_job.run(jobno);  
7、停止job: dbms.broken(jobno, broken, nextdate); –broken为boolean值 

二、初始化相关参数job_queue_processes
1、job_queue_process表示oracle能够并发的job的数量,当job_queue_process值为0时表示全部停止oracle的job。
2、查看job_queue_processes参数
方法一:
show parameter job_queue_process;
方法二:
select * from v$parameter where name=’job_queue_processes’;
3、修改job_queue_processes参数
alter system set job_queue_processes = 10;

三、user_jobs表结构
oracle job。字段(列)          类型                 描述
job                number          任务的唯一标示号
log_user           varchar2(30)    提交任务的用户
priv_user          varchar2(30)    赋予任务权限的用户
schema_user        varchar2(30)    对任务作语法分析的用户模式
last_date          date            最后一次成功运行任务的时间
last_sec           varchar2(8)    
如hh24:mm:ss格式的last_date日期的小时,分钟和秒
this_date          date           
正在运行任务的开始时间,如果没有运行任务则为null
this_sec           varchar2(8)    
如hh24:mm:ss格式的this_date日期的小时,分钟和秒
next_date          date            下一次定时运行任务的时间

以下使用一个案例来演示dbms_job的使用

一、在plsql中创建表:

1
2
3
4
create table t(
  id   varchar2(30),
  name varchar2(30)
);

二、在plsql中创建存储过程:

1
2
3
4
5
6
create or replace procedure proce_t is
begin
   insert into t(id, name) values('1', to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'));
   commit;
end proce_t;
/

三、创建job任务(1分钟执行一次):
在sql>后执行:

1
2
3
4
5
6
variable jobno number;
begin
   dbms_job.submit(:jobno,'proce_t;', sysdate, 'sysdate+1/24/60');
   commit;
end;
/

提交后提示:

1
2
3
4
pl/sql procedure successfully completed
jobno
---------
25

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

1
2
3
4
5
sql> select job, next_date, next_sec, failures, broken from user_jobs;
  
       job next_date   next_sec           failures broken
---------- ----------- ---------------- ---------- ------
        25 2012/9/14 1 10:59:46                  0 n

说明任务已创建成功。
执行select * from
t;查看定时任务的结果。可以看出定时任务是正常执行了的。
    
五、停止定时任务
1、查看定时任务的job号。

1
2
3
4
5
sql> select job, next_date, next_sec, failures, broken from user_jobs;
  
       job next_date   next_sec           failures broken
---------- ----------- ---------------- ---------- ------
        25 2012/9/14 1 11:01:48                  0 n

2、停止一个已启动的定时任务:

1
2
3
4
5
begin
   dbms_job.broken(25, true, sysdate);
   commit;
end;
/

表示停止job为25的任务。

执行后显示如下:
pl/sql procedure successfully completed

3、查看定时任务是否已停止成功

1
2
3
4
5
sql> select job, next_date, next_sec, failures, broken from user_jobs;
  
       job next_date   next_sec           failures broken
---------- ----------- ---------------- ---------- ------
        25 4000/1/1    00:00:00                  0 y

broken值为y,表示定时任务已停止。

六、启动定时任务
1、查看停止定时任务

1
2
3
4
5
sql> select job, next_date, next_sec, failures, broken from user_jobs;
  
       job next_date   next_sec           failures broken
---------- ----------- ---------------- ---------- ------
        25 4000/1/1    00:00:00                  0 y

broken值为y,表示定时任务已停止。

2、启动定时任务

1
2
3
4
5
begin
   dbms_job.run(25);
   commit;
end;
/

3、查看定时任务是否已启动

1
2
3
4
5
sql> select job, next_date, next_sec, failures, broken from user_jobs;
  
       job next_date   next_sec           failures broken
---------- ----------- ---------------- ---------- ------
        25 2012/9/14 1 11:06:17                  0 n

broken值为n,表示定时任务启动成功。

七、查看进程数
show parameter job_queue_processes;
必须大于0,否则执行下面的命令修改:
alter system set job_queue_processes=10;
 
八、再创建一个任务(每5分钟执行一次):

1
2
3
4
5
6
variable jobno number;
begin
   dbms_job.submit(:jobno, 'proce_t;', sysdate, 'sysdate+1/24/12'); --interval是以天为单位的
   commit;
end;
/

、 执行select job,next_date,next_sec,failures,broken from
user_jobs;
结果:

1
2
3
4
5
6
sql> select job,next_date,next_sec,failures,broken from user_jobs;
  
       job next_date   next_sec           failures broken
---------- ----------- ---------------- ---------- ------
        26 2012/9/14 1 11:12:08                  0 n
        25 2012/9/14 1 11:07:18                  0 n

十、总结
关于job运行时间
1:每分钟执行
Interval => TRUNC(sysdate,’mi’) + 1/(24*60)
2:每天定时执行
例如:每天的凌晨1点执行
Interval => TRUNC(sysdate) + 1 +1/(24)
3:每周定时执行
例如:每周一凌晨1点执行
Interval => TRUNC(next_day(sysdate,’星期一’))+1/24
4:每月定时执行
例如:每月1日凌晨1点执行
Interval =>TRUNC(LAST_DAY(SYSDATE))+1+1/24
5:每季度定时执行
例如每季度的第一天凌晨1点执行
Interval => TRUNC(ADD_MONTHS(SYSDATE,3),’Q’) + 1/24
6:每半年定时执行
例如:每年7月1日和1月1日凌晨1点
Interval => ADD_MONTHS(trunc(sysdate,’yyyy’),6)+1/24
7:每年定时执行
例如:每年1月1日凌晨1点执行
Interval =>ADD_MONTHS(trunc(sysdate,’yyyy’), 12)+1/24

job的运行频率设置
1.每天固定时间运行,比如早上8:10分钟:Trunc(Sysdate+1) +
(8*60+10)/24*60
2.Toad中提供的:
每天:trunc(sysdate+1)
每周:trunc(sysdate+7)
每月:trunc(sysdate+30)
每个星期日:next_day(trunc(sysdate),’星期日’)
每天6点:trunc(sysdate+1)+6/24
半个小时:sysdate+30/(24*60)
3.每个小时的第15分钟运行,比如:8:15,9:15,10:15…:trunc(sysdate,’hh’)+(60+15)/(24*60)。

=================================

凌晨2点执行

BEGIN cop_cust_pool_pkg.release_project; END ;

VARIABLE jobno number;
BEGIN
dbms_job.submit(:jobno,’cop_cust_pool_pkg.release_project;’,
sysdate, ‘trunc(sysdate)+2/24’);
commit;
end;
/
begin
dbms_job.broken(57818, true, sysdate);
commit;
end;
/
select job, next_date, next_sec, failures, broken from user_jobs;

JOB_QUEUE_INTERVAL=60JOB_QUEUE_PROCESSES=4job_queue_keep_connections=true

DBMS_JOB.SUBMIT(:jobno,//job号
’your_procedure;’,//要执行的过程
trunc(sysdate)+1/24,//下次执行时间
’trunc(sysdate)+1/24+1’//每次间隔时间
);
删除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);
例子:
VARIABLE jobno number;
begin
DBMS_JOB.SUBMIT(:jobno,
’Procdemo;’,
SYSDATE, ’SYSDATE + 1/720’);
commit;
end;

  

删除job:dbms_job.remove(jobno);

sql plus

2、示例,以下由sqlplus 来执行,具体参照一下相关的文档

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

然后重启一个库,如果原来已经打开了则不用这步了

 

解答:

 

1、把init.ora中如下两个参数打开

    

启动job:dbms_job.run(jobno);

 

修改job:dbms_job.what(jobno,what);

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

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

(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');

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

  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;  

例子:

 

VARIABLE jobno number;beginDBMS_JOB.SUBMIT(:jobno,’Procdemo;’,SYSDATE,
’SYSDATE + 1/720’);commit;end;

  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;  

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

表示停止job为1的任务。

问题:假设有一个存储过程p_test,如何才能每20分钟执行一次?

 

 

 六、查看进程数:

 

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

在pl/sql

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

sql> alter system enable restricted session;

sql> alter system disenable restricted session;

JOB NEXT_DATE NEXT_SEC FAILURES BROKEN

必须>0, 否则执行下面的命令修改:

  1. show parameter job_queue_processes;  

**

备注:

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

  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;  

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

创建存储过程

以查看定时任务的job号。

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

 

 

 

 

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

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

  1. PL/SQL procedure successfully completed  

每天3点执行

 

查看job

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

 

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

 

 

 

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

 

所以,创建一个任务的完整的格式是:

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

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

 

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

 

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

 

 

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

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

 

 

 

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

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

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

  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;

执行后显示如下:

创建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;

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

execute   immediate 

create or replace procedure truncate_table as
begin
       execute   immediate 'truncate table test_STATUS';
end;
select * from user_jobs;--查看当前用户的调度任务
select * from dba_jobs_running;--查看正在执行的调度任务
select * from dba_jobs;--查看执行完的调度任务
select * from all_jobs; -- 查看数据库系统所有任务

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

 

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

 

  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  

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

 

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

—–修改job_queue_processes的值(保证其不为0否则JOB不自动运行)

 

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.   );  
特别提示:
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;

 

**一、在PLSQL中创建表:

删除job:dbms_job.remove(jobno);

 

 

 

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

  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;  

  八、 执行作业查看

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

 

 

启动job:dbms_job.run(jobno);

  1. alter system set job_queue_processes=5;  

常用时间设置

 

结果:

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;
  1. select job,next_date,next_sec,failures,broken from user_jobs;  

 

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

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

 

在SQL>后执行

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

 

说明有一个任务存在了。

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

 

 方法2、alter   system   set   job_queue_processes=10

  1. create table HWQY.TEST   
  2.   
  3.   (   
  4.   
  5.   CARNO     VARCHAR2(30),   
  6.   
  7.   CARINFOID NUMBER   
  8.   
  9.   )  
  1. begin   
  2.   
  3.   dbms_job.remove(1);   
  4.   
  5.   commit;   
  6.   
  7.   end;  
  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.     

 

例子:

 

 

 

 

 

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

 

实验测试: