| 订阅 | 在线投稿
分享
 
 
 

Oracle 10g DBMS_SCHEDULER的中度解析

2008-08-15 05:14:52 编辑來源:互联网 国际版 评论
 
 
本文为【Oracle 10g DBMS_SCHEDULER的中度解析】的汉字拼音对照版显示拼音
  zhepianluntanwenzhangsaidiwangjishushequzhenduiDBMS_SCHEDULERde使shiyongfangfajinxinglexiangjindejieshaogengduoneirongqingshenkaoxiawen

  DBMS_SCHEDULERshiOracle 10Gzhongxinzengdeyigebaoyulaobanbendedbms_jobbaoxiangbidbms_scheduleryouhenduoxintexingwojiangtongguoyixiliedewenzhanglaijieshaoyixiaruhe使shiyongzhegebao.

  1. chuangjianjob

  jobshishenmene? jiandandeshuojiushijihua(schedule)jiashangrenwushuoming. lingwaihuanyouyixiebixudeshenshu.

  zhelitidaode"renwu"keyishishujukuneibudecunchuguocheng,nimingdePL/SQLkuai,yekeyishicaozuoxitongjibiedejiaoben.

  keyiyouliangzhongfangshilaidingyi"jihua":

  1) 使shiyongDBMS_SCHDULER.CREATE_SCHEDULE dingyiyigejihua;

  2) tiaoyongDBMS_SCHDULER.CREATE_JOBEguochengzhijiezhiding (xiamianhuixiangxishuoming)

  zaichuangjianyigejihuashinizhishaoxuyaozhidingxiamiandeshuxingtamenshijobyunxingsuobixude:

  kaishishijian (start_time);

  chongfupinlv (repeat_interval);

  jieshushijian (end_time)

  lingwaiduiyuyigejoberyanhuanyouhenduodefujiashenshu:

  job_class

  job_priority

  auto_drop

  restartable

  max_runs

  max_failures

  schedule_limit

  logging_level

  xiamianwoyiwendadexingshilaijutijieshi.

  Q1:zenmecongshujukuzhongchaxunjobdeshuxing ?

  A1: youliangzhongfangfa:

  1) chaxun(DBA|ALL|USER)_SCHEDULER_JOBS shitu

  (tishi: genjuyonghuquanxiandebutongxuanzexingdechaxun DBA|ALL|USERshitu)

  2) tiaoyongDBMS_SCHEDULERbaozhongdeGET_ATTRIBUTE guocheng

  Q2: zenmeshezhizhexieshuxingne?

  A2: yeshiyouliangzhongfangfa

  1) zaichuangjianjobshizhijiezhiding

  2) tiaoyongDBMS_SCHEDULERbaozhongdeSET_ATTRIBUTE guocheng

  Q3: "woxuyaoshenmequanxiancainengchuangjianjob" ?

  takeyichuangjianshuzhuweirenheyonghu(SYSyonghuchuwai)dejob.

  queshengqingkuangxia,jobhuibeichuangjianzaidangqiandeschemaxiabingqieshimeiyoujihuode; ruguoyao使shijobyichuangjian

  jiuzidongjihuoxuyaoxianshideshezhienabled shuxingweitrue, laikanyigelizi:

  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 Different Directory'

  );

  end;

  /

  Q4: nengbunengxiangxidijiangshuyixiashangmianzhegeguochengyongdaodegegeshenshu?

  A4:

  job_name: gumingsiyi,meigejobdoubixuyouyigedemingcheng

  schedule_name: ruguodingyilejihuazaizhelizhidingjihuademingcheng

  job_type: muqianzhichisanzhongleixing:

  PL/SQLkuai: PLSQL_BLOCK,

  cunchuguocheng: STORED_PROCEDURE

  waibuchengxu: EXECUTABLE (waibuchengxukeyishiyigeshelljiaoben,yekeyishicaozuoxitongjibiedezhiling).

  job_action: genjujob_typedebutongjob_actionyoubutongdehanyi.

  ruguojob_typezhidingdeshicunchuguochengjiuxuyaozhidingcunchuguochengdemingzi;

  ruguojob_typezhidingdeshiPL/SQLkuaijiuxuyaoshuruwanzhengdePL/SQLdaima;

  ruguojob_typezhidingdewaibuchengxujiuxuyaoshuruscriptdemingchenghuozhecaozuoxitongdezhilingming

  enabled: shangmianyijingshuoguolezhidingjobchuangjianwanbishifouzidongjihuo

  comments: duiyujobdejiandanshuoming

  2. zhidingjobdezhixingpinlv

  ruguowomenchuangjianleyigejob,bingqiexiwangtaanzhaowomenzhidingderiqiheshijianlaiyunxingjiuxuyaodingyijobdechongfupindule. lirumeitianyunxingmeizhouride22:00yunxing, meizhouyi,san,wuyunxingmeiniandezuihouyigexingqitianyunxingdengdeng.

  (shuoming:10GyiqiandebanbenyucaozuoxitongdejiaohufangmianshixiandebushihenhaoliruyaoshixianyigedingqidermanbeifenrenwujiuxuyaojieheOSdeminglinglaishixian,zaiUNIXxiakeyiyongcrontabshixianzaiwindowsxiayongATminglinglaishixian)

  10G zaizhefangmianyoulehendadezengqiangyinweichuangjianjobshikeyizhijiezhidingcaozuoxitongdeminglinghuozhejiaobenzaihelidedingyijobdezhixingpinlv,keyihenqingsongdiwanchengfuduodetiaodurenwu.

  10G zhichiliangzhongmoshiderepeat_interval,yizhongshiPL/SQLbiaodashizheyeshidbms_jobbaozhongsuo使shiyongde,liruSYSDATE+1, SYSDATE + 30/24*60; lingyizhongjiushirilibiaodashi

  liruMONbiaoshixingqiyi,SUNbiaoshixingqitian,DAYbiaoshimeitian,WEEKbiaoshimeizhoudengdeng. xiamianlaikanjige使shiyongrilibiaodashidelizi

  repeat_interval => 'FREQ=HOURLY; INTERVAL=2'

  meige2xiaoshiyunxingyicijob

  repeat_interval => 'FREQ=DAILY'

  meitianyunxingyicijob

  repeat_interval => 'FREQ=WEEKLY; BYDAY=MON,WED,FRI"

  meizhoude1,3,5yunxingjob

  repeat_interval => 'FREQ=YEARLY; BYMONTH=MAR,JUN,SEP,DEC; BYMONTHDAY=30'

  meiniande3,6,9,12yuede30haoyunxingjob

  yongguocrontabderenyinggaidouyouzhongsizengxiangshideganjiaobahehe

  xiamianzaishuoshuo使shiyongrilibiaodashideguize:

  rilibiaodashijibenfenweisanbufen: diyibufenshipinlvyejiushi"FREQ"zhegeguanjianzitashibixuzhidingde; dierbufenshishijianjiangeyejiushi"INTERVAL"zhegeguanjianziquzhifanweishi1-999. tashikexuandeshenshu; zuihouyibufenshifujiadeshenshu,keyongyujingquedizhidingriqiheshijian,tayeshikexuandeshenshu,liruxiamianzhexiezhidoushihefade:

  BYMONTH,BYWEEKNO,BYYEARDAY,BYMONTHDAY,BYDAY

  BYHOUR,BYMINUTE,BYSECOND

  xiangxideshenshushuomingqingshenkao dbms_schedulerde使shiyongshuoming.

  jiranshuodaolerepeat_interval,nikenengyaowen:"youmeiyouyizhongjian便biandefangfalaidechuhuozheshuoshipingguchujobdemeiciyunxingshijianyijixiayicideyunxingshijianne?"

  dbms_schedulerbaotigongleyigeguochengevaluate_calendar_string,keyihenfang便biandiwanchengzhegexuqiu. laikanxiamiandelizi:

  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;

  /

  shuchujieguoruxia:

  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的使用方法进行了详尽的介绍,更多内容请参考下文: DBMS_SCHEDULER是Oracle 10G中新增的一个包,与老版本的dbms_job包相比,dbms_scheduler有很多新特性,我将通过一系列的文章来介绍一下如何使用这个包. 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" ? 它可以创建属主为任何用户(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 Different Directory' ); 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
󰈣󰈤
 
 
>>返回首页<<
 为你推荐
 
 
 
 转载本文
 UBB代码 HTML代码
复制到剪贴板...
 
 
 热帖排行
 
 
王朝网络微信公众号
微信扫码关注本站公众号wangchaonetcn
 
  免责声明:本文仅代表作者个人观点,与王朝网络无关。王朝网络登载此文出于传递更多信息之目的,并不意味着赞同其观点或证实其描述,其原创性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
©2005- 王朝网络 版权所有