Oracle之Job应用

最近写服务，服务上线后，需要写一个定时执行的SQL脚本，清理并更新数据库表里的数据，应用到了Oracle 的 Job的相关知识。在此总结一下。

 

一：查看相关job信息

   1、相关视图 

dba_jobs 

all_jobs 

user_jobs 

dba_jobs_running 包含正在运行job相关信息。

 

     select * from dba_jobs ;

 

     --dba_jobs_running 包含正在运行job相关信息

select * from dba_jobs_running;

 

 

2、运行JOB 

说明：Run()过程用来立即执行一个指定的工作。这个过程只接收一个参数： 

SQL> begin 

2  dbms_job.run(:job); 

3  end; 

4  / 

 

----------------------------------------------------------------------------------

在plSQL中我的做法是：

begin

dbms_job.run(3017);

end; 

----------------------------------------------------------------------------------

 

3、删除JOB 

SQL> begin 

2  dbms_job.remove(:job);--:job可以用dba_jobs.job的值代替如：1198 

3  end; 

4  / 

 

二：参数：job_queue_process

 

pl/sql的命令窗口执行：

SQL> show parameter job_queue_process;

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

job_queue_processes                  integer     10

 

通过show parameter job_queue_process 来查看oracle中 job_queue_process的值。 当job_queue_process的值为0时表示全部停止oracle的job。

可以通过语句： ALTER SYSTEM SET job_queue_processes = 10;  来调整启动oracle的job。

 

视图dba_jobs字段的含义：

 

   JOB   任务的唯一标示号

         Identifier of job. Neither import/export nor repeated executions change it.

 

   LOG_USER     提交任务的用户

                USER who was logged in when the job was submitted

   PRIV_USER    赋予任务权限的用户

                USER whose default privileges apply to this job

   SCHEMA_USER  对任务作语法分析的用户模式

                select * from bar means select * from schema_user.bar

   LAST_DATE    最后一次成功运行任务的时间

                Date that this job last successfully executed

   LAST_SEC     如HH24:MM:SS格式的last_date日期的小时，分钟和秒

                Same as LAST_DATE. This is when the last successful execution started.

   THIS_DATE    正在运行任务的开始时间，如果没有运行任务则为null

                Date that this job started executing (usually null if not executing)

   THIS_SEC     如HH24:MM:SS格式的this_date日期的小时，分钟和秒

                Same as THIS_DATE. This is when the last successful execution started.

   NEXT_DATE    下一次定时运行任务的时间

                Date that this job will next be executed

 

   NEXT_SEC     如HH24:MM:SS格式的next_date日期的小时，分钟和秒

                Same as NEXT_DATE. The job becomes due for execution at this time.

   TOTAL_TIME    该任务运行所需要的总时间，单位为秒

                 Total wallclock time spent by the system on this job,in seconds

 

   BROKEN       标志参数，Y标示任务中断，以后不会运行

                If Y,no attempt is being made to run this job. See dbms_jobq.broken(job).

   INTERVAL      用于计算下一运行时间的表达式

                 A date function

 

   FAILURES     任务运行连续没有成功的次数

                How many times has this job started and failed since its last success?

   WHAT         执行任务的PL/SQL块

                Body of the anonymous PL/SQL block that this job executes

   NLS_ENV      任务运行的NLS会话设置

                alter session parameters describing the NLS environment of the job

   MISC_ENV     任务运行的其他一些会话参数

                a versioned raw maintained by the kernel

 

   INSTANCE

      Instance number restricted to run the job

 

 

 

－－－－－－－－－－－－－－－－－－－－－－－－－－

描述 INTERVAL参数值

 

每天午夜12点 'TRUNC(SYSDATE + 1)'

 

每天早上8点30分 'TRUNC(SYSDATE + 1) + （8*60+30）/(24*60)'

 

每星期二中午12点 'NEXT_DAY(TRUNC(SYSDATE ), ''TUESDAY'' ) + 12/24'

 

每个月第一天的午夜12点 'TRUNC(LAST_DAY(SYSDATE ) + 1)'

 

每个季度最后一天的晚上11点 'TRUNC(ADD_MONTHS(SYSDATE + 2/24, 3 ), 'Q' ) -1/24'

 

每星期六和日早上6点10分 'TRUNC(LEAST(NEXT_DAY(SYSDATE, ''SATURDAY"), NEXT_DAY(SYSDATE, "SUNDAY"))) + （6×60+10）/（24×60）'

 

－－－－－－－－－－－－－－－－－－－－－－－－－－

 

1:每分钟执行

 

Interval => TRUNC(sysdate,'mi') + 1/ (24*60)

 

或

 

Interval => sysdate+1/1440

 

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

 

 

---例子（第一个可运行的例子）：

 

1. 首先：创建一个package

 

create or replace package xinyangacitiviiy

is

procedure  proc_getxinyangRecommData;

end xinyangacitiviiy;

 

2. 其次：创建一个package body

 

create or replace package body xinyangacitiviiy is

procedure  proc_getxinyangRecommData as

BEGIN

       insert into ec.zhou_yhhd_jinfen values('1','1',to_char(sysdate,'yyyy-mm-dd HH:MM:SS'));

       COMMIT;

end;

end xinyangacitiviiy;

 

3.最后，定义job并实施运行策略

 

variable jobxinyang number;

begin

  sys.dbms_job.submit(job => :jobxinyang,

                      what => 'xinyangacitiviiy.proc_getxinyangRecommData;',

                      next_date => sysdate,

                      interval => 'TRUNC(sysdate,''mi'') + 1/ (24*60)');

  commit;

end;