SQL Exception ORA-27478: job "JOB_MIG_17602" is running during DBMS_SCHEDULER.run_job

Question

Getting the below exception when trying to run multiple jobs in parallel. This occurs intermittently.

The point of failure is DBMS_SCHEDULER.run_job

SQL Exception ORA-27478: job "JOB_MIG_17602" is running
"ORA-06512: at "SYS.DBMS_ISCHED", line 196
ORA-06512: at "SYS.DBMS_SCHEDULER", line 48

Description of the Job:

The job invokes a stored procedure which updates a table. The same stored procedure is invoked by different instances of the job created using unique job name.

Below are the steps to run the jobs:

DBMS_SCHEDULER.create_job(
    job_name                     => l_job_name,
    job_type                     => 'STORED_PROCEDURE',
    job_action                   => i_chunk_processor_name,
    number_of_arguments          => 2,
    enabled                      => FALSE,
    auto_drop                    => FALSE
);

DBMS_SCHEDULER.set_job_argument_value(job_name => l_job_name, argument_position => 1, argument_value => i_user_id);

DBMS_SCHEDULER.set_job_argument_value(job_name => l_job_name, argument_position => 2, argument_value => i_chunk_id);


DBMS_SCHEDULER.enable(l_job_name);

COMMIT;

DBMS_SCHEDULER.run_job(job_name => l_job_name, use_current_session => FALSE);

ok, that's not related to your case. there's a bug (M.O.S.-Doc ID 784664.1) with this issue but for db related to versions before 11.2. It's resolved in 11.2. — Barbaros Özhan, Dec 21 '17 at 07:53

score 0 · Accepted Answer · answered Dec 21 '17 at 01:16

Enabling a job means it is eligible for execution. So if the scheduler coordinator picks it up and starts it before you get to the "run_job" command, then you'll get the error, eg

SQL> begin
  2    DBMS_SCHEDULER.create_job(
  3      job_name                     => 'XXX',
  4      job_type                     => 'PLSQL_BLOCK',
  5      job_action                   => 'begin dbms_lock.sleep(60); end;',
  6      enabled                      => FALSE,
  7      auto_drop                    => FALSE
  8  );
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL> exec DBMS_SCHEDULER.enable('XXX');

PL/SQL procedure successfully completed.

-- I wait for a few seconds
--
SQL> exec DBMS_SCHEDULER.run_job(job_name => 'XXX', use_current_session => FALSE);
BEGIN DBMS_SCHEDULER.run_job(job_name => 'XXX', use_current_session => FALSE); END;

*
ERROR at line 1:
ORA-27478: job "MCDONAC"."XXX" is running
ORA-06512: at "SYS.DBMS_ISCHED", line 238
ORA-06512: at "SYS.DBMS_SCHEDULER", line 568
ORA-06512: at line 1

If you are enabling it, you should not need an explicit run_job request.

SQL Exception ORA-27478: job "JOB_MIG_17602" is running during DBMS_SCHEDULER.run_job

1 Answers1