Oracle Job维护

1. Job的创建

-- 创建JOB

BEGIN
  -- Job defined entirely by the CREATE JOB procedure.
  DBMS_SCHEDULER.create_job (
    job_name        => ‘test_full_job_definition‘,job_type        => ‘PLSQL_BLOCK‘,job_action      => ‘BEGIN my_job_proc(‘‘CREATE_PROGRAM (BLOCK)‘‘); END;‘,start_date      => SYSTIMESTAMP,repeat_interval => ‘freq=hourly; byminute=0‘,end_date        => NULL,enabled         => TRUE,comments        => ‘Job defined entirely by the CREATE JOB procedure.‘);
END;
/

BEGIN
  -- Job defined by an existing program and schedule.
  DBMS_SCHEDULER.create_job (
    job_name      => ‘test_prog_sched_job_definition‘,program_name  => ‘test_plsql_block_prog‘,schedule_name => ‘test_hourly_schedule‘,enabled       => TRUE,comments      => ‘Job defined by an existing program and schedule.‘);
END;
/

BEGIN
  -- Job defined by an existing program and inline schedule.
  DBMS_SCHEDULER.create_job (
    job_name        => ‘test_prog_job_definition‘,program_name    => ‘test_plsql_block_prog‘,comments        => ‘Job defined by existing program and inline schedule.‘);
END;
/

BEGIN
  -- Job defined by existing schedule and inline program.
  DBMS_SCHEDULER.create_job (
     job_name      => ‘test_sched_job_definition‘,job_type      => ‘PLSQL_BLOCK‘,job_action    => ‘BEGIN my_job_proc(‘‘CREATE_PROGRAM (BLOCK)‘‘); END;‘,comments      => ‘Job defined by existing schedule and inline program.‘);
END;
/

?

　2. ?Job 查询，启动,运行，停止,删除,`修改参数`

-- 查询job
select owner,job_name,state from dba_scheduler_jobs;
select * from user_scheduler_jobs
SELECT * FROM User_Scheduler_Job_Run_Details;
-- 启用job
begin
dbms_scheduler.enable(‘job_name‘);
end;
/
-- 运行job
begin
dbms_scheduler.run_job(‘job_name‘,TRUE); -- true代表同步执行
end;
/
-- 停止job(不太好用)
begin
dbms_scheduler.stop_job(job_name => ‘job_name‘,force => TRUE);
end;
/
-- 删除job(对停job来说好用)
begin
dbms_scheduler.drop_job(job_name => ‘job_name‘,force => TRUE);
end;
/
-- 删除job（2）
begin
dbms_scheduler.drop_job(‘job_name‘);
end;
-- 修改job参数
begin
dbms_scheduler.set_attribute(‘job_name‘,‘属性字段‘,‘属性内容‘);
END;
/

3.?Job的repeat_interval参数设置

-- calendar expression
repeat_interval=>‘Freq=Secondly;Interval=30‘; -- 每4小时
repeat_interval=>‘FREQ=HOURLY ; INTERVAL=4‘; -- 每4小时
repeat_interval=>‘FREQ=DAILY‘; -- 每天重复一次
repeat_interval=>‘FREQ=MINUTELY ; INTERVAL=15‘; -- 每15分钟重复一次
repeat_interval=>‘FREQ=YEARLY ;
BYMONTH=MAR,JUN,SEP,DEC;
BYMONTHDAY=15‘; -- 每年5,7,9,12月的15号重复一次
-- pl/sql expression
repeat_interval=>‘SYSDATE + 26/24‘; -- 每36小时重复一次
repeat_interval=>‘SYSDATE + 1‘; --每天重复一次
repeat_interval=>‘SYSDATE + 15/(24*60)‘; -- 每15分钟重复一次

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!