oracle 10g以上编写job sql语句

---要在system用户下赋create job权限给jobuser
grant create job to jobuser;
-----------------------------------------------------------------------------
---在jobuser下执行
----------定时执行job

BEGIN
  DBMS_SCHEDULER.CREATE_JOB(JOB_NAME        => 'test_job',--生成job名称
                            JOB_TYPE        => 'STORED_PROCEDURE',--job类型
                            JOB_ACTION      => 'PGEX_AUTOJOB.TESTJOB',--job执行的程序
                            START_DATE      => TRUNC(SYSTIMESTAMP + 1),--job开始执行时间
                            REPEAT_INTERVAL => 'FREQ=MINUTELY; INTERVAL=5;',--job执行频率
                            END_DATE        => SYSTIMESTAMP + 3000,--job结束执行时间
                            ENABLED         => TRUE,
                            AUTO_DROP       => FALSE,
                            COMMENTS        => '测试job');--job描述
END;
-----------------------------------
-- JOB 启用
BEGIN
  DBMS_SCHEDULER.ENABLE('test_job');
END;

 

 可以查询dba_scheduler_jobs表得到job的具体情况

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

---以下是9!数据库的

--生成job:test_job
VARIABLE job_busilog_addpartition NUMBER;
BEGIN
DBMS_JOB.SUBMIT(:job_busilog_addpartition,'PGEX_AUTOJOB.TESTJOB;',trunc(sysdate),'SYSDATE+1/24/60');
COMMIT;
end;

可以查询dba_jobs表得到job的具体情况

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

 

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

导读：

一、基本概念

    1.6 测试计划任务中设定的时间 dbms_scheduler.evaluate_calendar_string

二、郑彬的实践：

三、常用操作

四、查看job的状态

五、关于job_name的注意事项

六、参考文献

 

一、基本概念

1.1 oracle 10g以前的计划任务用的是DBMS_JOB包，10G以后用的是DBMS_SCHEDULER，

【问题1】：DBMS_SCHEDULER的新特性是什么呢？

【答案】：定义更灵活了；增强了和系统的交互性。

 

这里提到的"任务"可以是数据库内部的存储过程,匿名的PL/SQL块,也可以是操作系统级别的脚本.

 

1.2 可以有两种方式来定义"计划":

1) 使用DBMS_SCHDULER.CREATE_SCHEDULE  //定义一个计划，计划再调用job;

2) 调用DBMS_SCHDULER.CREATE_JOB   //过程直接定义job

 

1.3 这里引入了下面几个概念，从而使jobs可以完成复杂的任务。（类似，用户、组 的概念）

1，作业（job）

2，调度（scheduler）

3，程序（program）

4，链（chain）

5，作业类（job_class）

6，窗口（window）

7，窗口组（window_group）

 

1.4.1 例子：

创建的例子，重点解释每个参数的含义（其实还有其它参数，暂时还用不上，就不做过多了解）

【task1】：用DBMS_SCHDULER.CREATE_JOBE直接创建job

BEGIN

DBMS_SCHEDULER.CREATE_JOB (

   job_name           =>  'update_bb',   //job的名字

   job_type           =>  'STORED_PROCEDURE',   //job的类型为“执行存储过程”

   job_action         =>  'OPS.SALES_PKG.UPDATE_SALES_SUMMARY',    //存储过程的名字

   start_date         =>  '28-APR-10 07.00.00 PM Australia/Sydney',        //这里把时区去掉也可以！

   repeat_interval    =>  'FREQ=DAILY;INTERVAL=2', /* every other day */

   enabled =>true,      //启动该job

   comments           =>  'My new job');    //注释

END;

/

1.4.2 参数说明：

·job_name: 顾名思义,每个job都必须有一个的名称

·schedule_name: 如果定义了计划，在这里指定计划的名称

·job_type: 目前支持三种类型:

·PLSQL_BLOCK :  PL/SQL块: 需要输入完整的PL/SQL代码;

·STORED_PROCEDURE :  存储过程: 需要指定存储过程的名字;

·EXECUTABLE:  外部程序: (外部程序可以是一个shell脚本,也可以是操作系统级别的指令). 需要输入script的名称或者操作系统的指令名

·enabled: 上面已经说过了，指定job创建完毕是否自动激活

·comments: 对于job的简单说明

 

1.5 时间格式：

10G 支持两种模式的repeat_interval,一种是PL/SQL表达式，这也是dbms_job包中所使用的,例如SYSDATE+1, SYSDATE + 30/24*60; 另一种就是日历表达式。

 

例如MON表示星期一,SUN表示星期天,DAY表示每天,WEEK表示每周等等. 下面来看几个使用日历表达式的例子：

repeat_interval => 'FREQ=HOURLY; INTERVAL=2'   // 每隔2小时运行一次job

repeat_interval => 'FREQ=DAILY'      // 每天运行一次job

freq=daily;byhour=8,13,18;byminute=0;bysecond=0;bydate=0502,0922

根据上面的定义，jobs将会在 05/02 and 09/22 are 8:00 a.m., 1:00 p.m., and 6:00 p.m. 运行。

 

1.6 测试计划任务中设定的时间 dbms_scheduler.evaluate_calendar_string

SQL> run

  1  declare

  2  v_start_date timestamp;

  3  v_next_date timestamp;

  4  v_return_date timestamp;

  5  begin

  6  v_start_date := trunc(systimestamp);

  7  v_return_date := v_start_date;

  8  for qkk in 1..15 loop       //将来15次运行的时间

  9  dbms_scheduler.evaluate_calendar_string('freq = minutely;interval=1',v_start_date, v_return_date,v_next_date

 10  );       //测试蓝色部分的运行情况

 11  dbms_output.put_line('Next Run on: ' ||

 12  to_char(v_next_date,'yyyy-mm-dd hh24:mi:ss')

 13  );

 14  v_return_date := v_next_date;

 15  end loop;

 16* end;

运行结果：   //每分钟执行一次！

Next Run on: 2009-12-05 00:01:00

Next Run on: 2009-12-05 00:02:00

Next Run on: 2009-12-05 00:03:00

```````

Next Run on: 2009-12-05 00:14:00

Next Run on: 2009-12-05 00:15:00

 

PL/SQL procedure successfully completed.

 

二、郑彬的实践：

task1：建立job，让它自己单独运行

BEGIN

DBMS_SCHEDULER.CREATE_JOB (

   job_name            =>  'zbb_job3',

   job_type            =>  'PLSQL_BLOCK',    //job的类型是执行sql语句

   job_action          =>  'insert into bb values(1,sysdate);',

   start_date          =>   sysdate,

   repeat_interval     =>  'freq = minutely; interval=1',   //每分钟执行一次

   enabled             =>   true,

   comments            =>  'my love');

END;

/

 

注意： enabled 默认为false，oracle不会运行此job，所有我们需要enable它

 

SQL> exec dbms_scheduler.enable ('zbb_job3');

 

【task2】：建立 scheduler（控制时间和频率），然后给它添加几个jobs（动作）！

【task2.1】 创建scheduler “zbb_schedule”

BEGIN

DBMS_SCHEDULER.CREATE_SCHEDULE (           //创建计划任务

  schedule_name     => 'zbb_schedule',

  repeat_interval   => 'FREQ=MINUTELY; INTERVAL=5',    //执行间隔：每5分钟

  comments          => 'Every 5 MINUTS');

END;

/

 

【task2.2】 往“zbb_schedule”里添加一个job “zbb_job2”

BEGIN

DBMS_SCHEDULER.CREATE_JOB (      //创建job 并把它加入到scheduler里面

   job_name                 =>  'zbb_job2',

   job_type                 =>  'PLSQL_BLOCK',

   job_action               =>  'insert into bb values(1,sysdate);',

   schedule_name            =>  'ZBB_SCHEDULE');

END;

/

 

【task2.3】 往“zbb_schedule”里添加一个job “zbb_job3”

BEGIN

DBMS_SCHEDULER.CREATE_JOB (

   job_name                 =>  'zbb_job3',

   job_type                 =>  'PLSQL_BLOCK',

   job_action               =>  'insert into bb values(1,sysdate);',

   schedule_name            =>  'ZBB_SCHEDULE');

END;

/

 

SQL> select job_name,schedule_name from user_scheduler_jobs;

JOB_NAME                       SCHEDULE_NAME

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

ZBB_JOB1                                                        //说明它没有加入任何计划任务（scheduler）

ZBB_JOB2                       ZBB_SCHEDULE

ZBB_JOB3

 

task3: 将“zbb_job1” 加入到 “zbb_schedule”

SQL> begin

  2  dbms_scheduler.SET_ATTRIBUTE(      //注意这里执行的是修改属性的过程

  3  name => 'zbb_job1',

  4  attribute => 'schedule_name',

  5  value => 'ZBB_SCHEDULE');

  6  end;

  7  /

PL/SQL procedure successfully completed.

 

SQL> select job_name,schedule_name from user_scheduler_jobs;

JOB_NAME                       SCHEDULE_NAME

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

ZBB_JOB1                       ZBB_SCHEDULE

ZBB_JOB2                       ZBB_SCHEDULE

ZBB_JOB3

 

【task4】: 删除 job 及 scheduler

SQL> BEGIN

DBMS_SCHEDULER.DROP_JOB (           //删除job；多个job间用逗号隔开

   job_name   =>  'zbb_job2,zbb_job3',

   force      =>  TRUE);

END;

 

三、常用操作

如何删除job

BEGIN

DBMS_SCHEDULER.DROP_JOB (

   job_name    =>  'zbb_job1');

END;

/

 

如何删除正在运行的job

BEGIN

DBMS_SCHEDULER.DROP_JOB (

   job_name   =>  'zbb_job2,zbb_job3',

   force      =>  TRUE);

END;

/

 

如何删除scheduler

DBMS_SCHEDULER.DROP_SCHEDULE (

   schedule_name    => 'zbb_schedule',

   force            => true );   // 设为true 强制；false。

 

如何修改job的属性（frequency：频率）

BEGIN

DBMS_SCHEDULER.SET_ATTRIBUTE (

   name           =>   'zbb_job1',

   attribute      =>   'repeat_interval',

   value          =>   'FREQ=DAILY');    //每天执行一次

END;

/

 

时间设置

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_sched.htm#ARPLS138

freq=daily;byhour=8,13,18;byminute=0;bysecond=0;bydate=0502,0922

根据上面的定义，jobs将会在 05/02 and 09/22 are 8:00 a.m., 1:00 p.m., and 6:00 p.m. 运行。

 

start_date，其实是关于时区的设置！

?When start_date is NULL, the Scheduler will determine the time zone for the repeat interval as follows:

1.It will check whether the session time zone is a region name. The session time zone can be set by either:

Issuing an ALTER SESSION statement, for example:

 

SQL> ALTER SESSION SET time_zone = 'Asia/Shanghai';

Setting the ORA_SDTZ environment variable.

 

jobs创建时为“disabled”状态。我们必须手动“enable”它。

BEGIN

DBMS_SCHEDULER.ENABLE ('job1, job2, job3, 

   sys.jobclass1, sys.jobclass2, sys.jobclass3');   /* sys.jobclass1下的所有jobs都会被enable */

END;

/

 

四、查看job的状态

SELECT JOB_NAME, STATE FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'MY_EMP_JOB1';

JOB_NAME                       STATE

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

MY_EMP_JOB1                    DISABLED

 

Job         State  Description

disabled      The job is disabled.

scheduled      The job is scheduled to be executed.

running       The job is currently running.

completed     The job has completed, and is not scheduled to run again.

stopped       The job was scheduled to run once and was stopped while it was running.

broken        The job is broken.

failed        The job was scheduled to run once and failed.

succeeded      The job was scheduled to run once and completed successfully.

chain_stalled      The job is of type chain and has no steps running, no steps scheduled to run,

and no event steps waiting on an event, and the chain evaluation_interval is set to NULL.

No progress will be made in the chain unless there is manual intervention.

retry scheduled    The job has failed at least once and a retry has been scheduled to be executed.

 

Job Logs

SELECT JOB_NAME, OPERATION, OWNER FROM DBA_SCHEDULER_JOB_LOG;

 

Job 运行的详细情况

select log_id, job_name, status, to_char(log_date, 'DD-MON-YYYY HH24:MI') log_date 

from dba_scheduler_job_run_details

where job_name = 'MY_JOB14';

 

如何管理计划任务的权限

GRANT SCHEDULER_ADMIN TO username;

GRANT CREATE JOB TO scott;

GRANT ALTER myjob1 TO scott;

GRANT MANAGE SCHEDULER TO adam;

 

五、关于job_name的注意事项：

job_name => 'my_job'等价于 job_name => 'My_Job' 和 job_name => 'MY_JOB',

但是和 job_name => '"my_job"' 不同。

 

六、参考文献

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_sched.htm#ARPLS138