Job 管理

1.Creating a Job with In-Line Parameters

begin
  dbms_scheduler.create_job(
        job_name => 'test_job',
        job_type => 'plsql_block',
                 -- 指定job_action 中的执行内容
                 -- 1.plsql_block      ------- plsql块
                 -- 2.stored_procedure ------- 存储过程
                 -- 3.executable       ------- 操作系统的命令
        job_action => 'begin;end;',
        start_date => systimestamp,
                 -- job 的开始时间
        repeat_interval => 'FREQUENCY=HOURLY;INTERVAL=1',
                 -- 重复间隔
        enabled => true);
end;

2.Creating a Job Using a Program

begin
  dbms_scheduler.create_program(
      program_name => 'prog_name',
      program_type => 'plsql_block',
      program_action => 'begin;end;');
end;

begin
  dbms_scheduler.create_job('job_name1',
      program_name => 'prog_name',
      start_date => systimestamp,
      repeat_interval => 'freq=daily',
      enabled => true);
end;

3.Creating a Job for a Program with Arguments

--Create a program:
dbms_scheduler.create_program(
         program_name => 'prog_name',
         program_type => 'stored_procedure',
         program_action => 'EMP_REPORT');
         
--Define an argument:
dbms_scheduler.define_program_argument(
         program_name => 'prog_name',
         argument_name => 'dept_id',
         argument_position => 1, argument_type => 'number',
         default_value => '50');
         
--Create a Job specifying the number of arguments:
dbms_scheduler.create_job('job_name',
         program_name => 'prog_name',
         start_date => systimestamp,
         repeat_interval => 'freq=daily',
         number of arguments => 1,
         enabled => true); 

4.Creating a Job Using a Schedule

begin
  dbms_scheduler.create_schedule('sched_name',
    start_date => systimestamp,
    repeat_interval => 'freq=daily',
    end_date => systimestamp + 15);
end;

begin
  dbms_scheduler.create_job('job_name',
    schedule_name => 'sched_name',
    job_type => 'plsql_block',
    job_action => 'begin;end;',
    enabled => true);
end;

5.Creating a Job Using a Named Program and Schedule

begin
  dbms_scheduler.create_job('job_name',
    program_name => 'prog_name',
    schedule_name => 'sched_name',
    enabled => true);
end;

6.Setting the Repeat Interval for a Job

--日历表达式
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'    -- 每年3,6,9,12月的15号一次
--PL/SQL 表达式
repeat_interval => 'sysdate + 36/24'  -- 每36小时一次
repeat_interval => 'sysdate + 1'      -- 每小时一次
repeat_interval => 'sysdate + 15/(24*60)'  -- 15分钟一次

7.Managing Job

--Run a job
dbms_scheduler.run_job('schema.job_name');

--Stop a job
dbms_scheduler.stop_job('schema.job_name');

--Drop a job,even if it is currently running
dbms_scheduler.drop_job('job_name',true);

8.Related Data Dictionary View

[ DBA | ALL | USER ]_scheduler_jobs
[ DBA | ALL | USER ]_scheduler_running_jobs
[ DBA | ALL | USER ]_scheduler_job_classes
[ DBA | ALL | USER ]_scheduler_job_log
[ DBA | ALL | USER ]_scheduler_job_run_details
[ DBA | ALL | USER ]_scheduler_programs