Oracle job有定时执行的功能,可以在指定的时间点或每天的某个时间点自行执行任务,并且oracle里有dba_jobs与dba_scheduler_jobs两种类型的job,下面我将分别演示两种类型的Job使用方法。
-- 创建存储过程:
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;
-- 调用过程测试
begin
dbmsjob;
end;
-- 创建job:
DECLARE
job1 NUMBER;
begin
dbms_job.submit(job1,dbmsjob;,sysdate,sysdate+5/1440);
end;
查询生成的job id:
SQL> select job, what from dba_jobs;
-- 运行job:
begin
dbms_job.run(1);
end;
查询该job下次执行的时间:
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权限,对定时任务一些操作需要具有MANAGE SCHEDULER权限,如: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;
文章版权归作者所有,未经允许请勿转载,若此文章存在违规行为,您可以联系管理员删除。
转载请注明本文地址:https://www.ucloud.cn/yun/129177.html
摘要:类是一个抽象类,由安排为一次执行或重复执行的任务。也是自带的一个基于线程池设计的定时任务类。问题,则可以直接使用类实现自定义的定时调度规则。 定时调度作为后端开发人员,我们总会遇到这样的业务场景:每周同步一批数据;每半个小时检查一遍服务器运行状况;每天早上八点给用户发送一份包含今日待办事项的邮件,等等。 这些场景中都离不开定时器,就像一个定好时间规则的闹钟,它会在指定时间触发,执行我们...
阅读 1235·2023-01-11 13:20
阅读 1543·2023-01-11 13:20
阅读 996·2023-01-11 13:20
阅读 1652·2023-01-11 13:20
阅读 3958·2023-01-11 13:20
阅读 2456·2023-01-11 13:20
阅读 1290·2023-01-11 13:20
阅读 3454·2023-01-11 13:20