資訊專欄INFORMATION COLUMN
Oracle job有定時(shí)執(zhí)行的功能，可以在指定的時(shí)間點(diǎn)或每天的某個(gè)時(shí)間點(diǎn)自行執(zhí)行任務(wù),并且oracle里有dba_jobs與dba_scheduler_jobs兩種類型的job，下面我將分別演示兩種類型的Job使用方法。
-- 創(chuàng)建存儲(chǔ)過(guò)程：
create or replace procedure dbmsjob is
begin
execute immediate alter session set nls_date_format=YYYY-MM-DD HH24:MI:SS;
insert into zdq.job_date values(sysdate);
DBMS_LOCK.SLEEP(30);
COMMIT;
insert into zdq.job_date values(sysdate);
DBMS_LOCK.SLEEP(30);
COMMIT;
insert into zdq.job_date values(sysdate);
DBMS_LOCK.SLEEP(30);
COMMIT;
insert into zdq.job_date values(sysdate);
DBMS_LOCK.SLEEP(30);
COMMIT;
insert into zdq.job_date values(sysdate);
DBMS_LOCK.SLEEP(30);
COMMIT;
insert into zdq.job_date values(sysdate);
DBMS_LOCK.SLEEP(30);
COMMIT;
insert into zdq.job_date values(sysdate);
DBMS_LOCK.SLEEP(30);
COMMIT;
insert into zdq.job_date values(sysdate);
DBMS_LOCK.SLEEP(30);
COMMIT;
insert into zdq.job_date values(sysdate);
DBMS_LOCK.SLEEP(30);
COMMIT;
insert into zdq.job_date values(sysdate);
DBMS_LOCK.SLEEP(30);
COMMIT;
end dbmsjob;-- 調(diào)用過(guò)程測(cè)試
begin
dbmsjob;
end;-- 創(chuàng)建job：
DECLARE
job1 NUMBER;
begin
dbms_job.submit(job1,dbmsjob;,sysdate,sysdate+5/1440);
end;查詢生成的job id：
SQL> select job, what from dba_jobs;-- 運(yùn)行job：
begin
dbms_job.run(1);
end;查詢?cè)搄ob下次執(zhí)行的時(shí)間：
select job,next_date,what from dba_jobs where job=1;
create table zdq.job_date2 (id varchar2(100));
create or replace procedure dbmsjob2 is
begin
execute immediate alter session set nls_date_format=YYYY-MM-DD HH24:MI:SS;
insert into zdq.job_date2 values(sysdate);
DBMS_LOCK.SLEEP(30);
insert into zdq.job_date2 values(sysdate);
DBMS_LOCK.SLEEP(30);
insert into zdq.job_date2 values(sysdate);
DBMS_LOCK.SLEEP(30);
insert into zdq.job_date2 values(sysdate);
DBMS_LOCK.SLEEP(30);
insert into zdq.job_date2 values(sysdate);
DBMS_LOCK.SLEEP(30);
insert into zdq.job_date2 values(sysdate);
DBMS_LOCK.SLEEP(30);
insert into zdq.job_date2 values(sysdate);
DBMS_LOCK.SLEEP(30);
insert into zdq.job_date2 values(sysdate);
DBMS_LOCK.SLEEP(30);
insert into zdq.job_date2 values(sysdate);
DBMS_LOCK.SLEEP(30);
insert into zdq.job_date2 values(sysdate);
DBMS_LOCK.SLEEP(30);
COMMIT;
end dbmsjob2;
使用dbms_scheduler需要具有create job權(quán)限，對(duì)定時(shí)任務(wù)一些操作需要具有MANAGE SCHEDULER權(quán)限，如：dbms_scheduler.stop_job(my_job_test,true);
BEGIN
dbms_scheduler.create_job(job_name => dbms_scheduler_test,
job_type => STORED_PROCEDURE,
job_action => dbmsjob2,
start_date => sysdate,
repeat_interval => sysdate + 5/1440,
enabled => TRUE,
comments => dbms_scheduler_test);
end;
begin
dbms_scheduler.run_job(job_name => dbms_scheduler_test,
use_current_session => false);
end;
set lines 150
col OWNER for a20
col JOB_NAME for a20
col STATUS for a20
col ACTUAL_START_DATE for a20
col RUN_DURATION for a20
SELECT OWNER,JOB_NAME,STATUS,ACTUAL_START_DATE,RUN_DURATION,SESSION_ID,ADDITIONAL_INFO FROM DBA_SCHEDULER_JOB_RUN_DETAILS WHERE JOB_NAME=DBMS_SCHEDULER_TEST order by ACTUAL_START_DATE;
select owner,job_name,status,actual_start_date,run_duration,session_id,additional_info from dba_scheduler_job_run_details where job_name=dbms_scheduler_test;文章版權(quán)歸作者所有，未經(jīng)允許請(qǐng)勿轉(zhuǎn)載,若此文章存在違規(guī)行為，您可以聯(lián)系管理員刪除。
轉(zhuǎn)載請(qǐng)注明本文地址：http://systransis.cn/yun/129177.html
摘要：類是一個(gè)抽象類，由安排為一次執(zhí)行或重復(fù)執(zhí)行的任務(wù)。也是自帶的一個(gè)基于線程池設(shè)計(jì)的定時(shí)任務(wù)類。問(wèn)題，則可以直接使用類實(shí)現(xiàn)自定義的定時(shí)調(diào)度規(guī)則。 定時(shí)調(diào)度作為后端開(kāi)發(fā)人員，我們總會(huì)遇到這樣的業(yè)務(wù)場(chǎng)景：每周同步一批數(shù)據(jù)；每半個(gè)小時(shí)檢查一遍服務(wù)器運(yùn)行狀況；每天早上八點(diǎn)給用戶發(fā)送一份包含今日待辦事項(xiàng)的郵件，等等。 這些場(chǎng)景中都離不開(kāi)定時(shí)器，就像一個(gè)定好時(shí)間規(guī)則的鬧鐘，它會(huì)在指定時(shí)間觸發(fā)，執(zhí)行我們...
閱讀 1435·2023-01-11 13:20
閱讀 1796·2023-01-11 13:20
閱讀 1249·2023-01-11 13:20
閱讀 1988·2023-01-11 13:20
閱讀 4212·2023-01-11 13:20
閱讀 2849·2023-01-11 13:20
閱讀 1468·2023-01-11 13:20
閱讀 3766·2023-01-11 13:20
