Job 管理

最新推荐文章于 2023-05-17 17:45:40 发布

原创最新推荐文章于 2023-05-17 17:45:40 发布 · 894 阅读

0 ·

CC 4.0 BY-SA版权

Oracle 专栏收录该内容

23 篇文章

订阅专栏

本文深入探讨了数据库调度的基本概念、创建方法及关键步骤，包括使用内联参数创建作业、通过程序创建作业、为程序定义参数、使用调度创建作业、设置重复间隔以及管理作业的运行状态。此外，介绍了与数据库调度相关的数据字典视图，为读者提供了一个全面的指南，以高效地管理和执行数据库任务。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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