如何在Oracle中管理计划任务

Oracle 定时执行计划任务

Oracle 在10g 版本以前，计划任务用的是DBMS_JOB 包，10g 版本引入DBMS_SCHEDULER来替代先前的DBMS_JOB,在功能方面,它比DBMS_JOB 提供了更强大的功能和更灵活的机制管理，但DBMS_JOB 包的使用相对比较简单，也基本能够满足定时执行计划任务的需求，故接下来就先看看DBMS_JOB 包的使用方法。

1. DBMS_JOB

我们可以在命令窗口输入show parameter job_queue_processes 查看数据库中定时任务的最多并发数，一般设置为10（设置方法：alter system set job_queue_processes=10），如果设为0，那么数据库定时作业是不会运行的。

oracle 定时执行job queue 的后台进程是SNP,要启动snp,首先看系统模式是否支持sql>alter system enable restricted session或sql>alter system disenable restricted session利用上面的命令更改系统的会话方式为disenable restricted,为snp 的启动创建条件.

接下来我们尝试实现以下功能：每隔一分钟自动向job_test 表中插入当前的系统时间。

1、创测试表

create table job_test(updatetime date)

2、创建JOB

variable v_job_no number

begin

dbms_job.submit(:v_job_no, 'insert into job_test values(sysdate)', sysdate,'sysdate+1/1440')

end

/

其中最后一个参数'sysdate+1/1440'表示时间间隔为每分钟。其它常用的时间间隔的设置如下：

（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'。

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

1. 创建测试表

create table a(a date)

2. 创建一个存储过程

create or replace procedure test as

begin

insert into a values(sysdate)

end

/

3. 创建JOB（任务计划）

variable job1 number

begin

dbms_job.submit(:job1,'test',sysdate,'sysdate+1/1440')end

/

--每天1440分钟，即一分钟运行test存储过程一次4. 运行JOB

begin

dbms_job.run(:job1)

end

/

5. 查看执行结果

select to_char(a,'yyyy/mm/dd hh24:mi:ss') 时间 from a6.删除JOB

begin

dbms_job.remove(:job1)

end

/

job的使用说明：

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',//Procdemo为过程名称

SYSDATE, 'SYSDATE + 1/720')

commit

end

/

描述 Interval参数值

每天运行一次

'SYSDATE + 1'

每小时运行一次

'SYSDATE + 1/24'

每10分钟运行一次

'SYSDATE + 10/（60*24）'

每30秒运行一次

'SYSDATE + 30/(60*24*60)'

每隔一星期运行一次

'SYSDATE + 7'

不再运行该任务并删除它

NULL

每天午夜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）'

2. DBMS_SCHEDULER

DBMS_SCHEDULER的功能更强大，定义更灵活，增强了与系统的交互性。可以有两种方式来定义"计划"：

1) 使用DBMS_SCHDULER.CREATE_SCHEDULE //定义一个计划，计划再调用job2) 调用DBMS_SCHDULER.CREATE_JOB //过程直接定义job例子1：用DBMS_SCHDULER.CREATE_JOBE直接创建jobBEGIN

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=DAILYINTERVAL=2', /* every other day */enabled =>true, //启动该job

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

END

/

参数说明：

·job_name: 顾名思义,每个job都必须有一个的名称·schedule_name: 如果定义了计划，在这里指定计划的名称·job_type: 目前支持三种类型:

·PLSQL_BLOCK : PL/SQL块: 需要输入完整的PL/SQL代码·STORED_PROCEDURE : 存储过程: 需要指定存储过程的名字·EXECUTABLE: 外部程序: (外部程序可以是一个shell脚本,也可以是 *** 作系统级别的指令). 需要输入script的名称或者 *** 作系统的指令名·enabled: 上面已经说过了，指定job创建完毕是否自动激活·comments: 对于job的简单说明

例子2：

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 = minutelyinterval=1', //每分钟执行一次enabled =>true,

comments =>'my love')

END

/

注意： enabled 默认为false，oracle不会运行此job，所有我们需要enable它SQL>exec dbms_scheduler.enable ('zbb_job3')例子3：使用DBMS_SCHDULER.CREATE_SCHEDULE

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

BEGIN

DBMS_SCHEDULER.CREATE_SCHEDULE ( //创建计划任务schedule_name =>'zbb_schedule',

repeat_interval =>'FREQ=MINUTELYINTERVAL=5', //执行间隔：每5分钟comments =>'Every 5 MINUTS')

END

/

接下来往“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

/

再往“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

/

运行“select job_name,schedule_name from user_scheduler_jobs”可以查看到所有的job。

也可以将“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 /

删除 job 及 scheduler：

SQL>BEGIN

DBMS_SCHEDULER.DROP_JOB ( //删除job；多个job间用逗号隔开job_name =>'zbb_job2,zbb_job3',

force =>TRUE)

END

/

如何删除scheduler：

BEGIN

DBMS_SCHEDULER.DROP_SCHEDULE (

schedule_name =>'zbb_schedule',

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

END

/

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

BEGIN

DBMS_SCHEDULER.SET_ATTRIBUTE (

name =>'zbb_job1',

attribute =>'repeat_interval',

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

END

/

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

BEGIN

DBMS_SCHEDULER.ENABLE ('job1, job2, job3, sys.jobclass1, sys.jobclass2, sys.jobclass3')/* sys.jobclass1下的所有jobs都会被enable */END/END

/

查看job的状态：

SELECT JOB_NAME, STATE FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'MY_EMP_JOB1'查看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_detailswhere job_name = 'MY_JOB14'如何管理计划任务的权限：

GRANT SCHEDULER_ADMIN TO usernameGRANT CREATE JOB TO scottGRANT ALTER myjob1 TO scottGRANT MANAGE SCHEDULER TO adamPLSQL举例如下：

begin dbms_scheduler.create_job ( job_name =>'测试', job_type =>'PLSQL_BLOCK', job_action =>'insert into job_test values(sysdate)', start_date =>sysdate, end_date =>add_months(sysdate,12000), repeat_interval =>'FREQ=MINUTELYINTERVAL=1', enabled =>true,comments =>'定时执行计划任务' )

end

以上代码可以通过在PLSQL Developer中新建Jobs轻松设定：

参数含义如下：

?JOB_NAME ：指定任务的名称，必选值，注意要确保指定的名称唯一。

?JOB_TYPE ：任务执行的 *** 作类型，必选值，有下列几个可选值：

?PLSQL_BLOCK ：表示任务执行的是一个PL/SQL匿名块。

?STORED_PROCEDURE ：表示任务执行的是ORACLE过程(含PL/SQL PROCEDURE和JAVA PROCEDURE)。

?EXECUTABLE ：表示任务执行的是一个外部程序，比如说 *** 作系统命令。

?CHAIN ：表示任务执行的是一个CHAIN。

?JOB_ACTION ：任务执行的 *** 作，必选值，应与JOB_TYPE类型中指定的参数相匹配。比如说对于PL/SQL匿名块，此处就可以放置PL/SQL块的具体代表，类似DECLARE .. BEGIN ..END这类；如果是ORACLE过程，那么此处应该指定具体的过程名，注意由于任务执行，即使过程中有OUT之类参数，实际执行时也不会有输出的。

?START_DATE ：指定任务初次执行的时间，本参数可为空，当为空时，表示任务立刻执行，效果等同于指定该参数值为SYSDATE。

?REPEAT_INTERVAL ：指定任务执行的频率，比如多长时间会被触发再次执行。本参数也可以为空，如果为空的话，就表示当前设定的任务只执行一次。 REPEAT_INTERVAL参数需要好好说说，REPEAT_INTERVAL

1.引言

定制定时执行的任务有两种形式，系统级别和数据库级别，

从 *** 作系统级别来讲，

windows系统我们可以使用任务计划来实现，

对于winXP系统，设置步骤如下,开始---设置---控制面板---任务计划，然后添加任务计划，按向导完成配置即可

对于win7系统，设置步骤如下，点击开始,然后在 搜索程序和文件 框中输入 [任务计划]，然后点击出来的任务计划程序，创建一个基本任务即可；

linux系统我们可以使用crontab命令来是实现，

关于crontab命令的使用可以见之前的博客（http://www.2cto.com/os/201209/155632.html）；

从数据库级别来讲，我们可以采用数据库的job来实现；

本节主要介绍通过oracle数据库的job来定制一个简单的定时执行任务。本节会采用oracle定制一个定时向一个表中插入语句。

这里聊一下我做这个oracle定时任务的缘由：

项目中要在固定的时间，把一个数据库中某些表的数据同步到另外一个数据库，显然这些工作我不能每次都自己去做，

那么，我要怎么来做呢？这个时候oracle的定时执行任务JOB无疑是我的最佳选择。我把同步的脚本放在一个存储过程中，

然后在固定的时间去执行这个存储过程就OK了。

注意：以下所有的 *** 作都是在sytem用户下执行。采用PL/SQL做的客户端登陆。

2.项目环境

安装oracle10g，然后再安装PL/SQL，然后用管理员用户system登陆，

打开SQL Window输入图中的SQL语句， *** 作环境详细的信息如下：

3.环境准备

（1）新建一个表，命令为test，建表语句如下：

test0916

1 create table system.test0916 2 ( 3 seq NUMBER(2) PRIMARY KEY, 4 seqtime DATE 5 )

在SQL窗口执行以上语句；

（2）新建一个存储过程，命名为test_proc，存储过程的语句如下：

test_proc

1 CREATE OR REPLACE PROCEDURE test_proc IS 2 BEGIN 3 INSERT INTO SYSTEM.TEST0916(SEQ, SEQTIME) VALUES(NVL((SELECT MAX(SEQ) FROM SYSTEM.TEST0916) + 1, 0), SYSDATE)4 COMMIT5 EXCEPTION 6 WHEN OTHERS THEN 7 DBMS_OUTPUT.PUT_LINE('Exception happened,data was rollback')8 ROLLBACK9 END

在SQL Window窗口中执行以上存储过程，执行完成以后，查看Objects下的Procedures菜单，可以看到新建的存储过程如图：

TEST_PROC前面的图标没有红色的标记，表示存储过程正常；

（3）检查oracle的JOB运行环境

我这里遇到的只是要检查一下oracle的JOB队列的进程数，如果为0表示不运行JOB，

虽然网上说一般默认为10，但是我安装oracle以后就不知道为什么是0。

查看进程数：

修改进程数(如果你的是0，才需要按照以下语句修改)：

我这里已经改为10，假如我要改为11来模拟以下这个 *** 作；

在PL/SQL的command窗口中输入以下语句：

修改进程数

alter system set job_queue_processes=10 scope=both

运行截图如下：

修改完以后我们这查看一下：

看到了吧，我这是是改为11了吧。

补充说明：

网上有的说修改这个进程数可以修改oracle初始文件，最开始我是找到这个初始文件的位置，然后用notepad++打开后去修改，

保存以后我重启oracle服务和电脑，但是发现此时用PL/SQL登陆oracle的时候，一直报无法识别指定的字符串，这里要说明这样 *** 作不行。

这里有个问题，我们要如何找到oracle初始文件的位置？可以在PL/SQL的command window窗口中执行以下语句看到：

我当时用notepad++修改的是下图阴影部分的内容：

*.job_queue_processes=11

4.定制定时执行任务

假设我们要每隔两分钟向表test0916中插入一条记录，这里我们只需要每隔2分钟执行一次存储过程test_proc即可。

在PL/SQL的command window输入以下语句：

job

1 var job_num number2 begin 3dbms_job.submit(:job_num,'test_proc',SYSDATE,'sysdate+2/24/60')4 end

执行过程的截图如下：

执行完成以后记得点击PL/SQL的提交按钮；

5.查看执行结果

(1)查看我们新增的JOB是否成功

通过查看DBA_JOBS表中的WHAT字段可以看到最后一条记录即使我们刚新建的定时任务；

(2)查看test0916表是否插入了数据

6.其他相关说明

(1)如何删除一个JOB？

在PL/SQL的command window执行以下语句：

exec dbms_job.remove(81)

说明：这里的数字81是对应DBA_JOBS表中当前要删除的JOB记录所在行的JOB字段的值；

(2)submit的各个参数是什么意思？

使用Submit()过程，工作被正常地计划好。 这个过程有五个参数：job、what、next_date、interval与no_parse。 PROCEDURE Submit ( job OUT binary_ineger, What IN varchar2, next_date IN date, interval IN varchar2, no_parse IN booean:=FALSE)

job参数是由Submit()过程返回的binary_ineger。这个值用来唯一标识一个工作。 前面有一个双引号，在SQL windwo中没有显示，拷贝到command窗口就可以显示出来了； what参数是将被执行的PL/SQL代码块。这里是存储过程的名称，后面有一个英文状态下的分号不能掉了； next_date参数指识何时将运行这个工作。

interval参数何时这个工作将被重执行。 这里加号、括号都必须在英文状态下输入； no_parse参数指示此工作在提交时或执行时是否应进行语法分析——TRUE 指示此PL/SQL代码在它第一次执行时应进行语法分析， 而FALSE指示本PL/SQL代码应立即进行语法分析。

(3)定时执行的时间间隔是怎么设置的？

这里大家可以去百度，网上搜索一般都能找到满足你需求的，主要是结果TRUNC函数来实现。

---