如何创建异步Oracle作业以在多个实例中运行

BEGIN
   DBMS_SCHEDULER.create_program (program_name          => 'myProg',program_action        => 'myProc',program_type          => 'STORED_PROCEDURE',number_of_arguments   => 3,enabled               => FALSE);

   DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT (program_name        => 'myProg',argument_position   => 1,argument_type       => 'NUMBER');

   DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT (program_name        => 'myProg',argument_position   => 2,argument_position   => 3,argument_type       => 'NUMBER',DEFAULT_VALUE       => NULL);

   DBMS_SCHEDULER.create_job ('myJob',program_name   => 'myProg',enabled        => FALSE,comments       => 'Send data');

   DBMS_SCHEDULER.SET_ATTRIBUTE ('myJob','PARALLEL_INSTANCES',TRUE);
   DBMS_SCHEDULER.SET_ATTRIBUTE ('myJob','logging_level',DBMS_SCHEDULER.LOGGING_FULL);
END;
/

现在,我有一个用户可以运行/执行调用下一个过程的作业：

PROCEDURE runJOB(param1   IN PLS_INTEGER,param2   IN PLS_INTEGER DEFAULT NULL,param3   IN PLS_INTEGER DEFAULT NULL)
IS
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   DBMS_SCHEDULER.enable ('myProg');

   DBMS_SCHEDULER.set_job_argument_value ('myJob',1,TO_CHAR (param1));
   DBMS_SCHEDULER.set_job_argument_value ('myJob',2,TO_CHAR (param2));
   DBMS_SCHEDULER.set_job_argument_value ('myJob',3,TO_CHAR (param3));
   --DBMS_SCHEDULER.enable ('myJob');
   DBMS_SCHEDULER.RUN_JOB (JOB_NAME => 'myJob',USE_CURRENT_SESSION => FALSE);
--DBMS_SCHEDULER.disable ('myJob');
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END runJOB;

这有什么问题？

>我需要以异步模式运行作业.这就是为什么我有一个
使用USE_CURRENT_SESSION参数启用或运行
假.我觉得这很有效.
>我需要从头开始执行同一作业的多个实例
不同的用户,同时.例如,用户A调用
runJOB程序.这项工作可以在20秒内完成.在这20
秒,用户B可以在不同的会话中调用相同的过程.
这就是为什么我试图使用PARALLEL_INSTANCES属性,但是
我只得到一次执行.我认为Oracle认为这项工作是
运行,所以放弃第二次运行尝试.

在恢复时,我需要一个必须在异步模式下执行并且同时具有多个实例的作业.

在两个实例的“双重”执行作业后,我只在user_SCHEDULER_JOB_RUN_DETAILS表中获得一条记录,但是为两个不同的用户启用了2个作业(SGSS和EX01882_BD)

52367532    26/12/2016 12:08:44,584878 +00:00   SGSS    myJob DEFAULT_JOB_CLASS RUN SUCCEEDED                               (HugeClob)
52364238    26/12/2016 12:08:36,529539 +00:00   SGSS    myJob DEFAULT_JOB_CLASS ENABLE      EX01882_BD                          (HUGECLOB)
52367534    26/12/2016 12:08:34,302807 +00:00   SGSS    myJob DEFAULT_JOB_CLASS ENABLE      SGSS                            (HUGECLOB)

有帮助吗？

注意：
我不能像在此解决方案(How run two or more instances of an oracle job in the same time?)中那样拥有作业的不同名称,因为作业已经创建,并且调用此作业的用户没有创建权限.

解决方法

DBMS_SCHEDULER.RUN_JOB (JOB_NAME => ‘myJob’,USE_CURRENT_SESSION =>
FALSE);

现在,检查documentation：

This specifies whether or not the job run should occur in the same
session that the procedure was invoked from.

When use_current_session is set to TRUE:

• The job runs as the user who called RUN_JOB,or in the case of a local external job with a credential,the user named in the
  credential.

• You can test a job and see any possible errors on the command line.

• run_count,last_start_date,last_run_duration,and failure_count are not updated.

• RUN_JOB can be run in parallel with a regularly scheduled job run.

When use_current_session is set to FALSE:

• The job runs as the user who is the job owner.

• You need to check the job log to find error information.

• run_count,and failure_count are updated.

• RUN_JOB fails if a regularly scheduled job is running.

（编辑：李大同）

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