如何使用dbms_scheduler安排工作在特定时间运行 [英] How to schedule a work to run at particular time using dbms_scheduler

问题描述

目前尚不清楚，在这里，在DBMS_SCHEDULER中，我们有CREATE_PROGRAM CREATE_JOB CREATE_SCHEDULE等.在阅读了oracle文档之后，仍然不清楚该使用什么.在适当的时间排队，我计划创建调度程序以在特定时间执行它，然后创建程序以执行将消息排队的PL/SQL块，或者使用CREATE_SCHEDULE和CREATE_PROGRAM代替CREATE_JOB来完成这两项工作，使用哪个?请指导我是否做得正确，否则请纠正我.

Im not clear about this, here in DBMS_SCHEDULER we have CREATE_PROGRAM CREATE_JOB CREATE_SCHEDULE etc., after reading the oracle doc still im unclear what to use, On the Oracle side, i am going to use DBMS_SCHEDULER to insert a new message into the queue at the appropriate time, i planned to create scheduler to execute it on particular time and then create program to execute my PL/SQL block which will enqueue the message in the queue Or instead of using CREATE_SCHEDULE and CREATE_PROGRAM, CREATE_JOB does both the jobs, which to use? please guide me whether i am doing correctly, if not please correct me.

谢谢

推荐答案

create_job是安排呼叫的基本呼叫.您无需创建命名程序或时间表即可执行此操作.如果您有多个要使用此调用的作业，则在其中创建命名的程序/计划很有用.您可以只引用命名的计划时间表，而不必让每个作业都保留一份副本.

create_job is the basic call to schedule a call. you don't have to create a named program or schedule to do this. where creating a named program/schedule is useful, is if you have several jobs that want to use this call. you can just reference the named program schedule instead of having each job hold a copy of that.

例如如果您有5个作业要调用程序包MYPKG.ENTRY_PROG(param)，而每个作业只是使用不同的参数值，我想说的是，您要使用create_program定义该pl/sql调用，然后使用create_job引用该程序名称+设置选择的参数值.这样，如果您以后想要重命名API或其他名称，则无需更改五个单独的作业即可.

e.g. if you had 5 jobs wanting to call your package MYPKG.ENTRY_PROG(param) and each job just used a different parameter value, i'd say you want to use create_program to define that pl/sql call and then create_job to reference that program name + set the parameter value of choice. that way, if you want to rename the API later or something, you don't have to change five seperate jobs to do this.

如果您的工作只是一个独立的工作，它调用了不会被其他工作调用的例程，那么您不必使用create_program/create_schedule，只需直接使用create_job.

If your job is just a standalone job that calls a routine that won't be called by other jobs, then you don't have to use create_program/create_schedule, just use create_job directly.

我使用create_program的一个示例是调用测试工具.我的测试工具包称为pkg_test_harness.queue_tests(p_set_name in varchar2)，因此我定义了一些作业，这些作业使要在9 AM、12PM和5PM运行的各种API排队.而不是单独定义每个作业调用，我只是像这样呼叫create_program:

one example where I used create_program was to call a test harness. my test harness package is called pkg_test_harness.queue_tests(p_set_name in varchar2) so I have a few jobs defined that enqueues various APIs to be run at 9AM, 12PM and 5PM. instead of defining each jobs call separately i just called create_program like:

    dbms_output.put('Setting up TEST_HARNESS_ENQUEUE scheduler program...');
    dbms_scheduler.create_program(program_name        => 'TEST_HARNESS_ENQUEUE',
                                  program_type        => 'STORED_PROCEDURE',                                                          
                                  program_action      => 'pkg_test_harness.queue_tests', 
                                  number_of_arguments => 1,
                                  enabled             => false,
                                  comments            => 'Program to enqueue a set of API test for the test harness to run.');

    dbms_scheduler.define_program_argument(program_name      => 'TEST_HARNESS_ENQUEUE',
                                           argument_name     => 'p_set_name',
                                           argument_position => 1,
                                           argument_type     => 'VARCHAR2',
                                           default_value     => '');

    dbms_scheduler.enable (name => 'TEST_HARNESS_ENQUEUE');

    dbms_output.put_line('done.');

，然后定义每个作业"以指向程序.

and then each "job" was defined pointing to the program.

dbms_output.put('Setting up TEST_HARNESS_ENQUEUE_9AM scheduler job...');
dbms_scheduler.create_job(job_name        => 'TEST_HARNESS_ENQUEUE_9AM',
                          program_name    => 'TEST_HARNESS_ENQUEUE',
                          start_date      => systimestamp,
                          end_date        => null,
                          repeat_interval => 'freq=daily; byhour=9; byminute=0; bysecond=0;',
                          enabled         => true,
                          auto_drop       => false,
                          comments        => 'Job to enqueue a set of API test for the test harness to run.');

dbms_scheduler.set_job_argument_value(job_name          => 'TEST_HARNESS_ENQUEUE_9AM',
                                      argument_position => 1,
                                      argument_value    => 'DAILY_9AM');
dbms_output.put_line('done.');

dbms_output.put('Setting up TEST_HARNESS_ENQUEUE_12PM scheduler job...');
dbms_scheduler.create_job(job_name        => 'TEST_HARNESS_ENQUEUE_12PM',
                          program_name    => 'TEST_HARNESS_ENQUEUE',
                          start_date      => systimestamp,
                          end_date        => null,
                          repeat_interval => 'freq=daily; byhour=12; byminute=0; bysecond=0;',
                          enabled         => true,
                          auto_drop       => false,
                          comments        => 'Job to enqueue a set of API test for the test harness to run.');

我没有创建命名计划，因为这些计划对于单个作业是唯一的.

i didn't create a named schedule, as these schedules are unique to the individual job.

这篇关于如何使用dbms_scheduler安排工作在特定时间运行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！