一、查询现有定时任务
1. 查询数据库所有job:
select * from dba_scheduler_jobs;
2. 查询当前用户的job:
select * from User_Scheduler_Jobs;
二、创建定时任务模板:
begin
sys.dbms_scheduler.create_job(job_name => 'job_myjob', --job名称
job_type => 'STORED_PROCEDURE', --job类型
job_action => '存储过程名称', --可以是 STORED_PROCEDURE、PLSQL_BLOCK、EXECUTABLE、CHAIN
start_date => to_date('30-12-2018 01:00:00', 'dd-mm-yyyy hh24:mi:ss'), --job开始生效日期
repeat_interval => 'Freq=Daily;Interval=1;BYHOUR=9;BYMINUTE=30;BYSECOND=0', --执行计划具体时间,这里意思是:每天9:30分执行一次,Interval时间间隔可省略;
end_date => to_date(null), --job失效日期
job_class => 'DEFAULT_JOB_CLASS',
enabled => true, --job启用(enable)/禁用(disable)
auto_drop => false, --job禁用后是否自动删除
comments => 'job备注信息');
end;
job_type 有 STORED_PROCEDURE、PLSQL_BLOCK、EXECUTABLE、CHAIN 4种。
repeat_interval 重复间隔,有 Yearly、Monthly、Weekly、Daily、Hourly、Minutely、Secondly 7种。
三、示例:
begin
sys.dbms_scheduler.create_job(job_name => 'ENABLE_OLD_500PTLACCOUNT',
job_type => 'STORED_PROCEDURE',
job_action => 'ENABLE_OLD_PTLACCOUNT',
start_date => to_date('2023-1-7 00:00:00', 'yyyy-mm-dd hh24:mi:ss'),
repeat_interval => 'Freq=Daily;BYHOUR=8;BYMINUTE=0;BYSECOND=0',
end_date => to_date('2023-2-7 00:00:00', 'yyyy-mm-dd hh24:mi:ss'),
job_class => 'DEFAULT_JOB_CLASS',
enabled => true,
auto_drop => false,
comments => '定时任务备注');
end;
其中存储过程信息如下:
CREATE OR REPLACE PROCEDURE ENABLE_OLD_PTLACCOUNT
IS
BEGIN
UPDATE LR_BASE_USER SET F_ENABLEDMARK = 1,SYNCNOTE = 0 WHERE EMP_NO IN ('50007534','00130134');
END ENABLE_OLD_PTLACCOUNT;
查询刚刚创建的定时任务:
select * from User_Scheduler_Jobs;
四、Oracle定时任务常用操作
运行:
begin
dbms_scheduler.run_job('job_myjob');
end;
启用:
begin
dbms_scheduler.enable('job_myjob');
end;
禁用:
begin
dbms_scheduler.disable('job_myjob');
end;
刪除:
begin
dbms_scheduler.drop_job(job_name => 'job_myjob',force => TRUE);
end;