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.
