Question: How to create a async Oracle job to run in multiple instances

Question

How to create a async Oracle job to run in multiple instances

Answers 1
Added at 2016-12-26 12:12
Tags
Question

I have created the next program and subsequent Oracle JOB:

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_type       => 'NUMBER');

   DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT (program_name        => 'myProg',
                                           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;
/

Now, I have a user who can run/execute jobs that calls the next procedure:

    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;

What are my problems here?

  • I need to run the job in asynchronous mode. That's why I have a enable or a run_job using the USE_CURRENT_SESSION parameter FALSE. I think this works.
  • I need to execute multiple instances of the same job, starting by different users, at the same time. For example, user A calls the runJOB procedure. The job can run in 20 seconds. In this 20 seconds, user B can call the same procedure, in a different session. That's why I've tried to use the PARALLEL_INSTANCES attribute, but I get only one execution. I think Oracle sees that the job is running, so discard the second attempt to run.

In resuming, I need a job that must be executed in async mode and with multiple instances at the same time.

After a "double" execution of the job for two instances I only get one record in user_SCHEDULER_JOB_RUN_DETAILS table, but 2 enable job's for two different users (SGSS and 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)

Any help?

Note: I cannot have job's different names as in this solution (How run two or more instances of an oracle job in the same time?), because the job is already created and the users who call this job don't have permissions to create.

Answers
nr: #1 dodano: 2016-12-27 09:12

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

now, check 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, last_start_date, last_run_duration, and failure_count are updated.

  • RUN_JOB fails if a regularly scheduled job is running.

Source Show
◀ Wstecz