Getting the below exception when trying to run multiple jobs in parallel. Each job is created with a unique name by appending with a sequence . Also This occurs intermittently.
The point of failure is DBMS_SCHEDULER.run_job. DBMS_SCHEDULER.stop_job or DBMS_SCHEDULER.drop_job are not invoked explicitly.
> 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 requirement is to update a table with huge data volume. The approach taken is to chunk the data in the table and invoke a service per chunk.
To achieve this - the service is invoked per chunk using DBMS_SCHEDULER.
Below are the steps to run the jobs:
FOR i in 1..number_of_jobs LOOP
l_seq := seq_job_id.next_val;
l_job_name := 'JOB_'|| l_seq ;
DBMS_SCHEDULER.create_job(
job_name => l_job_name,
job_type => 'STORED_PROCEDURE',
job_action => 'MIGRATE_PKG.UPDATE_LOT',
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 => user_id);
DBMS_SCHEDULER.set_job_argument_value(job_name => l_job_name, argument_position => 2, argument_value => l_seq );
DBMS_SCHEDULER.enable(l_job_name);
COMMIT;
DBMS_SCHEDULER.run_job(job_name => l_job_name, use_current_session => FALSE);
END LOOP;
Is it happening because oracle is trying to drop a running job?