ORACLE调度(schedule)详解(转)

偏执的太偏执、 2022-05-09 11:24 1342阅读 0赞

### part 1 ###

### 1. 创建job    ###

job是什么呢? 简单的说就是计划(schedule)加上任务说明. 另外还有一些必须的参数.  
  
这里提到的"任务"可以是数据库内部的存储过程,匿名的PL/SQL块,也可以是操作系统级别的脚本.  
  
可以有两种方式来定义"计划":  
1) 使用DBMS\_SCHDULER.CREATE\_SCHEDULE 定义一个计划;  
2) 调用DBMS\_SCHDULER.CREATE\_JOBE过程直接指定 (下面会详细说明)  
  
在创建一个计划时，你至少需要指定下面的属性，它们是job运行所必须的:  
  
开始时间 (start\_time);  
  
重复频率 (repeat\_interval);  
  
结束时间 (end\_time)  
  
  
另外，对于一个job而言，还有很多的附加参数:  
  
job\_class  
job\_priority  
auto\_drop  
restartable  
max\_runs  
max\_failures  
schedule\_limit  
logging\_level  
  
  
下面，我以问答的形式来具体解释.  
  
  
Q1:怎么从数据库中查询job的属性 ?  
  
A1: 有两种方法:  
  
1) 查询(DBA|ALL|USER)\_SCHEDULER\_JOBS 视图  
(提示: 根据用户权限的不同，选择性的查询 DBA|ALL|USER视图)  
  
2) 调用DBMS\_SCHEDULER包中的GET\_ATTRIBUTE 过程  
  
  
Q2: 怎么设置这些属性呢?  
  
A2: 也是有两种方法  
  
1) 在创建job时直接指定  
  
2) 调用DBMS\_SCHEDULER包中的SET\_ATTRIBUTE 过程  
  
  
Q3: "我需要什么权限才能创建job" ?  
  
A3: 你至少需要create\_job这个系统权限。如果用户拥有create any job这个权限，  
它可以创建属主为任何用户(SYS用户除外)的job.  
  
缺省情况下,job会被创建在当前的schema下，并且是没有激活的; 如果要使job一创建  
就自动激活，需要显式的设置enabled 属性为true, 来看一个例子:  
  
begin  
dbms\_scheduler.create\_job  
(  
job\_name => 'ARC\_MOVE',  
schedule\_name => 'EVERY\_60\_MINS',  
job\_type => 'EXECUTABLE',  
job\_action => '/home/dbtools/move\_arcs.sh',  
enabled => true,  
comments => 'Move Archived Logs to a DifferentDirectory'  
);  
end;  
/  
  
  
Q4: 能不能详细地讲述一下上面这个过程用到的各个参数?  
  
A4:  
job\_name: 顾名思义,每个job都必须有一个的名称  
schedule\_name: 如果定义了计划，在这里指定计划的名称  
job\_type: 目前支持三种类型:  
  
PL/SQL块: PLSQL\_BLOCK,  
存储过程: STORED\_PROCEDURE  
外部程序: EXECUTABLE (外部程序可以是一个shell脚本,也可以是操作系统级别的指令).  
  
job\_action: 根据job\_type的不同，job\_action有不同的含义.  
如果job\_type指定的是存储过程，就需要指定存储过程的名字;  
如果job\_type指定的是PL/SQL块，就需要输入完整的PL/SQL代码;  
如果job\_type指定的外部程序，就需要输入script的名称或者操作系统的指令名  
  
enabled: 上面已经说过了，指定job创建完毕是否自动激活  
comments: 对于job的简单说明

### 2. 指定job的执行频率 ###

如果我们创建了一个job,并且希望它按照我们指定的日期和时间来运行，就需要定义  
job的重复频度了. 例如每天运行，每周日的22:00运行, 每周一,三,五运行，每年的  
最后一个星期天运行等等.  
(说明:10G以前的版本，与操作系统的交互方面，实现的不是很好。例如要实现一个  
定期的rman备份任务，就需要结合OS的命令来实现,在UNIX下可以用crontab实现，  
在windows下用AT命令来实现)  
  
10G 在这方面有了很大的增强，因为创建job时可以直接指定操作系统的命令或者  
脚本，再合理的定义job的执行频率,可以很轻松地完成复杂的调度任务.  
  
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  
  
repeat\_interval => 'FREQ=WEEKLY;BYDAY=MON,WED,FRI"  
每周的1,3,5运行job  
  
repeat\_interval => 'FREQ=YEARLY;BYMONTH=MAR,JUN,SEP,DEC; BYMONTHDAY=30'  
每年的3,6,9,12月的30号运行job  
  
用过crontab的人应该都有种似曾相识的感觉吧，呵呵  
  
  
下面再说说使用日历表达式的规则:  
  
日历表达式基本分为三部分: 第一部分是频率，也就是"FREQ"这个关键字，  
它是必须指定的; 第二部分是时间间隔，也就是"INTERVAL"这个关键字，  
取值范围是1-999. 它是可选的参数; 最后一部分是附加的参数,可用于  
精确地指定日期和时间,它也是可选的参数,例如下面这些值都是合法的:  
BYMONTH,BYWEEKNO,BYYEARDAY,BYMONTHDAY,BYDAY  
BYHOUR,BYMINUTE,BYSECOND  
  
详细的参数说明请参考 dbms\_scheduler的使用说明.  
  
既然说到了repeat\_interval,你可能要问:"有没有一种简便的方法来得出，  
或者说是评估出job的每次运行时间，以及下一次的运行时间呢?"  
  
dbms\_scheduler包提供了一个过程evaluate\_calendar\_string,可以很  
方便地完成这个需求. 来看下面的例子:  
  
SQL> set serveroutput on size 999999  
  
SQL> declare  
L\_start\_date TIMESTAMP;  
l\_next\_date TIMESTAMP;  
l\_return\_date TIMESTAMP;  
  
begin  
l\_start\_date := trunc(SYSTIMESTAMP);  
l\_return\_date := l\_start\_date;  
for ctr in 1..10 loop  
dbms\_scheduler.evaluate\_calendar\_string(  
'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=7,15',  
l\_start\_date, l\_return\_date, l\_next\_date  
);  
  
dbms\_output.put\_line('Next Run on: ' ||  
to\_char(l\_next\_date,'mm/dd/yyyy hh24:mi:ss')  
);  
  
l\_return\_date := l\_next\_date;  
  
end loop;  
end;  
/  
  
  
输出结果如下:  
  
Next Run on: 03/22/2004 07:00:00  
Next Run on: 03/22/2004 15:00:00  
Next Run on: 03/23/2004 07:00:00  
Next Run on: 03/23/2004 15:00:00  
Next Run on: 03/24/2004 07:00:00  
Next Run on: 03/24/2004 15:00:00  
Next Run on: 03/25/2004 07:00:00  
Next Run on: 03/25/2004 15:00:00  
Next Run on: 03/26/2004 07:00:00  
Next Run on: 03/26/2004 15:00:00  
  
  
下一部分我将总结一下如何用dbms\_scheduler包来创建和管理  
程序(Program),计划(Schedule).  
  
part2

### 3. 创建程序 (program)    ###

什么是程序? 我的理解就是准备计划需要的元数据(metadata),它  
包括以下部分:  
  
程序名;  
  
程序中用到的参数: 例如程序的类型，以及具体操作的描述  
  
来看一个例子  
begin  
dbms\_scheduler.create\_program(  
program\_name=> 'DAILY\_BACKUP\_SH',  
program\_type=> 'EXECUTABLE',  
program\_action=>'/home/oracle/script/daily\_backup.sh');  
end;  
/  
  
这个例子将会创建一个名为"DAILY\_BACKUP\_SH"的程序，类型是可执行的shell脚本，脚本的名称是“/home/oracle/script/daily\_backup.sh”  
  
  
Q1: 程序和作业相比，有什么区别呢?  
  
A1: 程序其实是可以与作业分离的，因此不同的用户可以在不同的时间段去重用它.而一个作业是属于特定的用户的;  
  
另外，将程序与作业分离，也就激活了一个新的程序库(ProgramLibrary),利用程序库,用户可以很自由地选择特定的程序在特定的时间段运行，以及自由的配置程序执行时的参数.  
  
  
Q2: 能否解释一下 create\_program与create\_job的关系?  
  
A2: 首先，你应该知道创建程序并不是一个计划的必须组成部分，一个计划可以没有程序,但是必须有一个已经定义好的作业;  
另外,program\_action这个参数也是可选的，假如程序的类型是pl/sql 块,你完全可以在创建作业时来指定它.  
  
上面已经提到了，程序和作业可以是分离的，这样一个程序的具体执行(ACTION)就可以灵活地确定。它既可以只运行一次，也可以在多个不同的作业中来重用这个执行.这样一来，在修改针对一个作业的计划时就非常灵活，你不需要重新创建pl/sql块.  
  
  
Q3: 运行 create\_program需要什么权限 ?  
  
A3: 要保证create\_program能够顺利执行，你同样需要CREATE JOB这个系统权限. 如果一个用户拥有了createany job这个权限，它就可以创建属主为任何用户的程序(SYS用户除外)  
  
与创建作业一样，一个程序建立完毕，缺省的状态也是非激活的,当然你可以在创建程序时，显式的设置enabled参数为true来  
激活它.  
  
  
Q4: 能否介绍一下create\_program这个过程的各个参数?   
更多的例子?  
  
A4:  
program\_name: 指定程序的名称;  
  
program\_type: 目前只支持下面三种:  
STORED\_PROCEDURE  
PLSQL\_BLOCK  
EXECUTABLE  
  
program\_action: (有长度限制,因为类型是varchar2)  
  
存储过程的名称;  
具体的pl/sql代码  
操作系统脚本名称  
  
  
来看一个使用pl/sql块的例子  
BEGIN  
DBMS\_SCHEDULER.CREATE\_PROGRAM(  
program\_name => 'LEO.UPDATE\_STATS',  
program\_type => 'PLSQL\_BLOCK',  
  
program\_action => 'DECLARE  
sUsername varchar2(30);  
  
cursor cur is select username from dba\_users  
where username not in ('SYS','SYSTEM','SYSMAN','DBSNMP')  
and account\_status='OPEN' andsubstr(username,1,5)<>'MGMT\_' ;  
  
BEGIN  
  
OPEN cur;  
FETCH cur into sUsername;  
  
WHILE cur%Found  
LOOP  
DBMS\_STATS.GATHER\_SCHEMA\_STATS (sUsername);  
  
FETCH cur into sUsername;  
END LOOP;  
  
close cur;  
  
END;');  
  
END;  
/  
  
上面这个例子创建一个名为"UPDATE\_STATS"的程序，它的类型是PL/SQL块，完成更新非系统用户的统计信息的工作。在这个基础上你可以定制一个合理的计划，来定期执行这个程序.

### 4. 配置程序的参数 ###

先来看一个例子  
  
begin  
dbms\_scheduler.create\_program(  
program\_name=> 'LEO.UPDATE\_STATS\_2',  
program\_action=> 'LEO.UPDATE\_SCHEMA\_STATUS');  
program\_type=> 'STORED\_PROCEDURE',  
number\_of\_arguments => 1,  
enabled => TRUE);  
  
dbms\_scheduler.define\_program\_argument(  
program\_name=> 'UPDATE\_STATS\_2',  
argument\_name => 'SCHEMA\_NAME',  
argument\_position => 1,  
argument\_type => 'varchar2',  
default\_value => 'HR');  
end;  
/  
  
这个例程是不是很像上面的那个例子呢? 对,它们的区别只是  
在这个例子中,程序的类型是存储过程，而不是pl/sql块.  
  
解释:  
使用define\_program\_argument这个过程来定义一个程序所需要的参数. 有两点说明一下:  
  
1) 程序如果使用了参数，就必须事先指定，这样才能在程序被job使用时生效;  
  
2)定义程序的参数不会改变程序的激活属性。也就是说，如果一个程序是没有激活的状态,运行了define\_program\_argument过程不会自动激活这个程序.  
  
  
关于权限:  
缺省情况下只有program的owner才能修改创建的程序，如果用户被授予了alter 权限 或者 create anyjob权限，就可以修改属主为另一个用户的程序.

### 5. 创建计划(Schedule) ###

其实，如果你已经了解了怎样创建作业和程序，就等于已经掌握怎样创建计划了。你需要做的附加工作只是指定计划的开始时间，结束时间，重复频率等等.  
  
来看一个例子  
  
begin  
dbms\_scheduler.create\_job(  
job\_name=> 'leo.UPDATE\_STATS\_JOB',  
program\_name=> 'leo.UPDATE\_STATS\_2',  
start\_date=>'2005-06-20 11:00.00.000000 PM+8:00',  
repeat\_interval=>'FREQ=MONTHLY;INTERVAL=1',  
end\_date=>'2006-06-20 11:00.00.000000 PM+8:00',  
comments=>'Monthly statistics collectionjob');  
end;  
/  
  
start\_date和end\_date这两个参数需要说明一下: 它们的数据类型  
是timestamp，因此需要精确的指定时间和时区. 时间格式继承的是NLS\_DATE\_FORMAT这个初始化参数的值.  
  
下一部分介绍:

1) 配置作业的参数;  
2) 创建/使用/管理作业,程序,计划所需要的系统权限.  
  
一些补充   
1.在10G R2 中, 如果用dbms\_scheduler创建的job的类型为executable, 需要createexternal job 权限。  
  
2. 在使用create\_job或者create\_schedule前，请先检查  
NLS\_DATE\_LANGUAGE, NLS\_DATE\_FORMAT,   
NLS\_TIMESTAMP\_FORMAT, NLS\_TIMESTAMP\_TZ\_FORMAT  
等参数的值, 通过alter session 命令来修改  
  
例如:   
SQL> select \* from nls\_session\_parameters;  
  
alter session set NLS\_DATE\_LANGUAGE='AMERICAN';  
alter session set NLS\_DATE\_FORMAT='dd-mm-yyyy hh24:mi:ss';  
alter session set NLS\_TIMESTAMP\_TZ\_FORMAT='dd-mm-yyyy HH:MI:SS.FFAM TZR'  
  
begin  
dbms\_scheduler.create\_job(  
job\_name=> 'zip\_emlog',  
job\_type=> 'EXECUTABLE',  
job\_action =>'/home/leo/zip\_log.sh',  
enabled=>true,  
start\_date=>'03-07-2005 9:30:00 PM + 8:00',  
repeat\_interval=>'FREQ=MINUTELY;INTERVAL=30',  
end\_date=>'31-07-2005 9:30:00 PM + 8:00',  
comments=>'Get a latest em log copy and compress itevery 30 minutes');  
end;  
/

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

### oracle中创建作业 ###

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

用法DEMO:    
     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);

附：调用语句和参数说明：

dbms\_job.submit( job out binary\_integer,

what　　　　　　　in　　　archar2,  
next\_date　　　 　in　　　date，  
interval　　　　　in　　　varchar2,  
no\_parse　　　　　in　　　boolean)  
其中：  
●    job：输出变量，是此任务在任务队列中的编号；  
●    what：执行的任务的名称及其输入参数；注意任务名称后要有分号，下例中：***'MY\_Procedure****;****'***  
●    next\_date：任务执行的时间；  
●    interval：任务执行的时间间隔。

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

**一、创建一个存储过程**  
  
***create or replaceprocedure MY\_Procedure  
is  
         \--这里用到了游标   
         cursor cur is  
             select \* from users where length(name) > 10;  
begin  
         for user in cur  
         loop  
             insert into users\_tempvalues(user.id,user.passwd);    
         end loop;  
end;*  
  
二、创建作业**  
  
***variable job\_num number;   
begin   
         dbms\_job.submit(:job\_num,'MY\_Procedure****;****',sysdate,'sysdate+1');   
         commit;   
end;***   
  
**三、运行作业**  
  
***begin   
         dbms\_job.run(:job\_num);   
end;***   
  
**四、查询作业**  
  
***select priv\_user,job,whatfrom user\_jobs;***  
  
**五、删除作业**   
  
***begin   
         dbms\_job.remove(:job\_num);   
end;*  
  
六、时间  
  
*Sysdate＋1/1440***                        每1分钟执行一次  
***Sysdate＋5/1440                    *** 每5分钟执行一次  
***trunc(sysdate+1)+2/24           ***下一天的2点（凌晨）执行一次  
***'SYSDATE +7'                         ***最后一次执行的7天之后执行  
***'SYSDATE +1/48'***                       每半个小时执行一次  
***'NEXT\_DAY(TRUNC(SYSDATE),''MONDAY'') +15/24'                                  ***每个礼拜一的下午3点  
***'NEXT\_DAY(ADD\_MONTHS(TRUNC(SYSDATE,''Q''),3),''THURSDAY'')'***         每个季度的第一个星期四

转自：http://blog.sina.com.cn/s/blog\_4449f2f90100j63b.html