Oracle定時(shí)執(zhí)行計(jì)劃任務(wù).

1、Oracle 定時(shí)執(zhí)行計(jì)劃任務(wù)Oracle 在 10g 版本以前,計(jì)劃任務(wù)用的是 DBMS_JOB 包，10g 版本引入DBMS_SCHEDULER 來(lái)替代先前的 DBMS_JOB,在功能方面，它比 DBMS_JOB 提 供了更強(qiáng)大的功能和更靈活的機(jī)制 管理，但 DBMS_JOB 包的使用相對(duì)比較簡(jiǎn)單,也 基本能夠滿足定時(shí)執(zhí)行計(jì)劃任務(wù)的需求,故接下來(lái)就先看看 DBMS_JOB 包的使用 方法。1. DBMS_JOB我們可以在命令窗口輸入 show parameter job_queue_processe 查看數(shù)據(jù)庫(kù)中定 時(shí)任務(wù) 的最多并發(fā)數(shù),一般設(shè)置為 10(設(shè)置方法：alter system

2、 set job_queue_processes=10如果 設(shè)為 0,那么數(shù)據(jù)庫(kù)定時(shí)作業(yè)是不會(huì)運(yùn)行的。oracle 定時(shí)執(zhí)行 job queue 的后臺(tái)進(jìn)程是 SNP ,要啟動(dòng) snp,首先看系統(tǒng)模式是 否支持 sql alter system en able restricted session? sql alter system dise nable restrictedsessi on 利用上面的命令更改系統(tǒng)的會(huì)話方式為dise nable restricted 為 snp的啟動(dòng)創(chuàng)建條件接下來(lái)我們嘗試實(shí)現(xiàn)以下功能：每隔一分鐘自動(dòng)向 job_test 表中插入當(dāng)前的系統(tǒng) 時(shí)間。1、創(chuàng)測(cè)試表

3、create table job_test(updatetime date;2、創(chuàng)建 JOBvariable v_job_ no nu mber;begi ndbms_job.submit(:v_j ob_no, in sert into job_test values(sysdate;, sysdate,sysdate+1/1440;end;/其中最后一個(gè)參數(shù)sysdate+1/1440 表示時(shí)間間隔為每分鐘。其它常用的時(shí)間間 隔的設(shè)置如下：(1 如果想每天凌晨 1 點(diǎn)執(zhí)行，則此參數(shù)可設(shè)置為trunc(sysdate+25/24;(2 如果想每周一凌晨 1 點(diǎn)執(zhí)行，則此參數(shù)可設(shè)置為tru n

4、c( next_day(sysdate,1+25/24;(3 如果想每月 1 號(hào)凌晨 1 點(diǎn)執(zhí)行,則此參數(shù)可設(shè)置為tru nc(last_day(sysdate+25/24;(4 如果想每季度執(zhí)行一次，則此參數(shù)可設(shè)置為tru nc(add_mo nths(sysdate,3,Q+1/24;SQL J mho謝psram etfer j ob_qntu_pr r.ct s sesNAMEmiVALUEjraciEEE10system set jS5=1O2 /System(5 如果想每半年執(zhí)行一次,則此參數(shù)可設(shè)置為add_ mon ths(tru nc(sysdate,yyyy,6+1/24;(

5、6 如果想每年執(zhí)行一次，則此參數(shù)可設(shè)置為add_mo nths(tru nc(sysdate,yyyy,12+1/24select * from user_jobs;-查看當(dāng)前用戶的調(diào)度任務(wù)select * from dba_jobs_ru nnin g;-查看正在執(zhí)行的調(diào)度任務(wù)select * from dba_jobs;-查看執(zhí)行完的調(diào)度任務(wù)select * from all_jobs;-查看數(shù)據(jù)庫(kù)系統(tǒng)所有任務(wù)實(shí)例 1：1. 創(chuàng)建測(cè)試表create table a(a date;2. 創(chuàng)建一個(gè)存儲(chǔ)過(guò)程create or replace procedure test asbegi nin s

6、ert into a values(sysdate;end;/3. 創(chuàng)建 JOB (任務(wù)計(jì)劃variable job1 nu mber;begi ndbms_job.submit(:job1,test;,sysdate,sysdate+1/1440; end;-每天 1440 分鐘,即一分鐘運(yùn)行 test 存儲(chǔ)過(guò)程一次4. 運(yùn)行 JOBbegi ndbms_job.r un(:jobl;end;/5. 查看執(zhí)行結(jié)果select to_char(a,yyyy/mm/dd hh24:mi:ss 時(shí)間 from a; 6.刪除 JOB begi ndbms_job.remove(:job1;end;

7、/job 的使用說(shuō)明：DBMS_JOB.SUBMIT(:jobno,/job 號(hào)your_procedure;,/要 執(zhí)行的過(guò)程trun c(sysdate+1/24,/下 次執(zhí)行時(shí)間tru nc(sysdate+1/24+17 每次間隔時(shí)間刪除 job:dbms_job.remove(job no;修改要執(zhí)行的操作：job:dbms_job.what(jobno,what;修改下次執(zhí)行時(shí)間:dbms_job.next_date(job,next_date; 修改間隔時(shí)間:dbms_erval(job,interval;停止 job:dbms.broke n(job,broke

8、n,n extdate;啟動(dòng) job:dbms_job.r un (job no;例子：VARIABLE job no number;begi nDBMS_JOB.SUBMIT(:job no,Procdemo;,/Procdemo 為過(guò)程名稱SYSDATE, SYSDATE + 1/720;commit;end;描述Interval參數(shù)值每尺運(yùn)行一次*SYSDATE + lr每小時(shí)運(yùn)行一次*SYSDATE + 1/24*每10分鐘運(yùn)行次*SYSDATE + 10/ (60* 24 J *每30杪運(yùn)行一次*SYSDATE + 30/(60*24*60)r每隔一星期運(yùn)行一次*SYSDATE +

9、T不再運(yùn)行該任務(wù)井刪除它NULL每天午夜12點(diǎn)RUNCfSYSDATE + l)1每夭早上8點(diǎn)30分RUNCfSYSDATE + 1) +(8*60+30) /(24*60)*每星期二中午12點(diǎn)* N EXT DAY( T RU NC( SYS DAT E ), TUESDAY ) + 12/2每個(gè)力第-天的午夜匹點(diǎn)*TRU NC(LAST DAY( SYS DATE ) + l)1每個(gè)李度最后一處的晚上11點(diǎn)tTRUNC(ADD MONTHS(SYSDATE + 2/24, 2 ), Q ) 1每星期六和日早上6點(diǎn)10分*TRUNC( LEAST( NEXT_DAY(SYSDATE, *SA

10、TU RDAY,)/NEXT-DAYfSYSDATE,ISUNDAYH) +(6x60+10) /(24x 60)12. DBMS_SCHEDULERDBMS_SCHEDULER 的功能更強(qiáng)大，定義更靈活，增強(qiáng)了與系統(tǒng)的交互性?？梢?有兩種方 式來(lái)定義計(jì)劃:1 使用 DBMS_SCHDULER.CREATE_SCHEDULE /定義一個(gè)計(jì)戈 U，計(jì)戈 U 再調(diào)用 job;2 調(diào)用 DBMS_SCHDULER.CREATE_JOB / 過(guò)程直接定義 job例子 1:用 DBMS_SCHDULER.CREATE_JOBE 直接創(chuàng)建 jobBEGINDBMS_SCHEDULER.CREATE_JOB

11、 (job_n ame = update_bb, /job 的名字job_type = STORED_PROCEDURE, /job 的類型為 執(zhí)行存儲(chǔ)過(guò)程 ” job_action =OPS.SALES_PKG.UPDATE_SALES_SUMMARY, / 存儲(chǔ)過(guò)程的 名字start_date = 28-APR-10 07.00.00 PM Australia/Sydney,這里把時(shí)區(qū)去掉也可 以！repeatnterval = FREQ=DAILY;INTERVAL=2, /* every other day */enabled =true, /啟動(dòng)該 jobcomme nts = My

12、 new job; 注釋END;/參數(shù)說(shuō)明：job_name:顧名思義，每個(gè) job 都必須有一個(gè)的名稱schedule_name:如果定義了計(jì)劃，在這里指定計(jì)劃的名稱job_type:目前支持三種類型：PLSQL_BLOCK : PL/SQL 塊：需要輸入完整的 PL/SQL 代碼；STORED_PROCEDURE :存儲(chǔ)過(guò)程：需要指定存儲(chǔ)過(guò)程的名字；EXECUTABLE:外部程序：（外部程序可以是一個(gè) shell 腳本，也可以是操作系 統(tǒng)級(jí)別的指令.需要輸入 script 的名稱或者操作系統(tǒng)的指令名enabled:上面已經(jīng)說(shuō)過(guò)了，指定 job 創(chuàng)建完畢是否自動(dòng)激活comme nts:對(duì)于

13、job 的簡(jiǎn)單說(shuō)明例子 2:BEGINDBMS_SCHEDULER.CREATE_JOB (job_ name = zbb_job3,job_type = PLSQL_BLOCK, /job 的類型是執(zhí)行 sql 語(yǔ)句 job_action = insertinto bb values(1,sysdate;,start_date = sysdate,repeat_interval = freq = minutely; interval=1, 每分鐘執(zhí)行一 次en abled = true,comme nts = my love;END;/注意：enabled 默認(rèn)為 false , oracl

14、e 不會(huì)運(yùn)行此 job，所有我們需要 enable 它SQL exec dbms_scheduler.e nable (zbb_job3;例子 3:使用 DBMS_SCHDULER.CREATE_SCHEDULE建立 scheduler 控制時(shí)間和頻率撚后給它添加幾個(gè) jobs (動(dòng)作！BEGINDBMS_SCHEDULER.CREATE_SCHEDULE ( / 倉(cāng) U 建計(jì)戈 U 任務(wù)schedule_ name = zbb_schedule,repeat_interval = FREQ=MINUTELY; INTERVAL=5, / 執(zhí)行間隔：每 5 分鐘 comme nts = Eve

15、ry 5MINUTS;END;/接下來(lái)往 “ zbb_schedul 里添加一個(gè) job “ zbb_job2 ”：BEGINDBMS_SCHEDULER.CREATE_JOB ( / 創(chuàng)建 job 并把它加入到 scheduler 里面 job_n ame =zbb_job2,job_type = PLSQL_BLOCK,job_action = in sert into bb values(1,sysdate;,schedule_name = ZBB_SCHEDULE;END;/再往 “ zbb_schedul 里添加一個(gè) job “ zbb_job3 ”：BEGINDBMS_SCHEDU

16、LER.CREATE_JOB (job_n ame = zbb_job3,job_type = PLSQL_BLOCK,job_action = in sert into bb values(1,sysdate;,schedule_name = ZBB_SCHEDULE;END;運(yùn)行 “ select job_name,schedule_name from user_scheduler_jo!S 以查看到所有 的 job。也可以將 “ zbb_job1 加入到 “ zbb_schedule ”SQL begi n2 dbms_scheduler.SET_ATTRIBUTE( 注意這里執(zhí)行的是修

17、改屬性的過(guò)程3 n ame = zbb_job1,4 attribute = schedule, name,5 value = ZBB_SCHEDULE;6 end;7 /刪除 job 及 scheduler:SQL BEGINDBMS_SCHEDULER.DROP_JOB ( / 刪除 job ;多個(gè) job 間用逗號(hào)隔開(kāi)job_ name = zbb_job2,zbb_job3,force = TRUE;END;/如何刪除 scheduler :BEGINDBMS_SCHEDULER.DROP_SCHEDULE ( schedule_name = zbb_schedule, force E

18、ND; / 如何修改 job 的屬性(frequency:頻率)：BEGIN DBMS_SCHEDULER.SET_ATTRIBUTE ( n ame attribute valueEND; / = = = zbb_job1, repeatnterval, FREQ=DAILY; / 每天執(zhí)行一次= true ; / 設(shè)為 true 強(qiáng) 制；falseo jobs 創(chuàng)建時(shí)為“disabled 犬態(tài)。我們必須手動(dòng) “enable 它：BEGIN DBMS_SCHEDULER.ENABLE(job1, job2, job3, sys.jobclass1, sys.jobclass2, sys.jo

19、bclass3; /* sys.jobclass1 下的所有 jobs 都會(huì)被enable */END;/ END; / 查看 job 的狀態(tài)：SELECT JOB_NAME, STATE FROM DBA_SCHEDULER_JOBSWHERE JOB_NAME = MY_EMP_JOB1;查看 Job Logs： SELECT JOB_NAME, OPERATION, OWNERFROM DBA_SCHEDULER_JOB_LOG;查看 Job 運(yùn)行的詳細(xì) 情況：select log_id, job_name, status,to_char(log_date, DD-MON-YYYY HH

20、24: MI log_date from dba_scheduler_job_ru n_detailswhere job_name = MY_JOB14 如何管理 計(jì)劃任務(wù)的權(quán)限： GRANT SCHEDULER_ADMIN TO username;GRANTCREATEJOB TO scott; GRANT ALTER myjob1 TO scott;GRANT MANAGE SCHEDULER TO adam; PLSQL 舉例如下： beg in dbms_scheduler.create_job ( job_ name =測(cè)試, job_type = PLSQL_BLOCK, job_

21、action= in sert into job_test values(sysdate;, start_date = sysdate, en d_date = add_m on ths(sysdate,12000,repeat_i nterval = FREQ=MINUTELY; INTERVAL=1, en abled = true,comments =定時(shí)執(zhí)行計(jì)劃任務(wù);end;以上代碼可以通過(guò)在 PLSQL Developer 中新建 Jobs 輕松設(shè)定：參數(shù)含義如下：？JOB_NAME :指定任務(wù)的名稱，必選 值，注意要確保指定的名稱唯一。？JOB_TYPE :任務(wù)執(zhí)行的操作類型，必選

22、值，有下列幾個(gè)可選值：?PLSQL_BLOCK :表示任務(wù)執(zhí)行的是一個(gè) PL/SQL 匿名塊。?STORED_PROCEDURE :表示任務(wù)執(zhí)行的是 ORACLE 過(guò)程（含 PL/SQL PROCEDURE 和 JAVAPROCEDURE。EXECUTABLE :表示任務(wù)執(zhí)行的是一個(gè) 外部程序，比如說(shuō)操作系統(tǒng)命令。?CHAIN :表示任務(wù)執(zhí)行的是一個(gè) CHAIN。?JOB_ACTION :任務(wù)執(zhí)行的操作， 必選值， 應(yīng)與 JOB_TYPE 類型中指定的參數(shù)相 匹配。 比如說(shuō)對(duì)于 PL/SQL匿名塊，此處就可以放置 PL/SQL 塊的具體代表，類似 DECLARE . BEGIN .END 這類

23、；如果是 ORACLE過(guò)程，那么此處應(yīng)該指定具體 的過(guò)程名，注意由于任務(wù) 執(zhí)行，即使過(guò)程中有 OUT 之類參數(shù)，實(shí)際執(zhí)行時(shí)也不會(huì) 有輸出的。?START_DATE :指定任務(wù)初次執(zhí)行的時(shí)間，本參數(shù)可為空，當(dāng)為空時(shí)，表示任務(wù)立刻執(zhí)行，效果等同于指定該參數(shù)值為 SYSDATE。?REPEAT_INTERVAL :指定任務(wù)執(zhí)行的頻率，比如多長(zhǎng)時(shí)間會(huì)被觸發(fā)再次執(zhí)行。本參數(shù) 也可以為空，如果為空的話，就表示當(dāng)前設(shè)定的任務(wù)只執(zhí)行一次。REPEAT_INTERVAL 參數(shù)需要好好說(shuō)說(shuō)，REPEAT_INTERVAL 參數(shù)的語(yǔ)法結(jié)構(gòu) 要復(fù)雜的多。其中最重要的是 FREQ 和 INTERVAL 兩個(gè)關(guān)鍵字。扌

24、REQ 關(guān)鍵字用 來(lái)指定間隔的時(shí)間周期，可選參數(shù)有：YEARLY, MONTHLY, WEEKLY, DAILY,HOURLY, MINUTELY, SECONDLY ，分別表示年、月、周、日、時(shí)、分、秒等單 位。INTERVAL 關(guān)鍵字用來(lái)指定間隔的頻繁，可指定的值的范圍從1-99。例女口： REPEAT_INTERVAL=FREQ=DAILY;INTERVAL=1;表示每天執(zhí)行一次，女口 果將 INTERVAL改為 7 就表示每 7 天執(zhí)行一次，效果等同于FREQ=WEEKLY;INTERVAL=1 。一般來(lái)說(shuō)，使用 DBMS_SCHEDULER.CREATE_JOB 創(chuàng)建一個(gè) JOB,至少需要指定上述參數(shù)中 的 前 3 項(xiàng)。除此之外，還可以在 CREATE_JOB 時(shí)，指定下列參數(shù)：?NUMBER_OF_ARGUMENTS :指定該 JOB 執(zhí)行時(shí)需要附帶的參數(shù)的數(shù)量，默認(rèn) 值為 0,注意當(dāng)JOB_TYPE 列值為 PLSQL_BLOCK 或 CHAIN 時(shí)，本參數(shù)必須設(shè) 置為 0,因?yàn)樯鲜鰞煞N情況 下不支持附帶參數(shù)。？END_DATE :指定任務(wù)的過(guò)期 時(shí)間，默認(rèn)值為 NULL。任務(wù)過(guò)期后，任務(wù)的 STATE 將自動(dòng)被修改為 COMPLETED，ENABLED 被置為 FALSE。如果該參數(shù)設(shè)置為空的話，表示該任 務(wù)永不過(guò)期，將一直按照