Can't Delete Scheduled Job : ORA-27475 or ORA-27478 (Doc ID 1290836.1)

Last updated on DECEMBER 06, 2016

Applies to:

Oracle Database - Standard Edition - Version 11.2.0.2 and later
Information in this document applies to any platform.
***Checked for relevance on 29-Aug-2016***

Symptoms


o Db version 11.2.0.1
o Trying to delete a job with force option using DBMS_SCHEDULER.DROP_JOB

can result into one of the following errors:

ORA-27475: "STPJWS.JO_BS_00000_STP4911" must be a job
ORA-06512: at "SYS.DBMS_ISCHED", line 213
ORA-06512: at "SYS.DBMS_SCHEDULER", line 657
ORA-06512: at line 1

ORA-27478: job "JWS_WAP.JD_BW_00018_D_ADVW.S_ADVW" is active
ORA-06512: at "SYS.DBMS_ISCHED", regel 213
ORA-06512: at "SYS.DBMS_SCHEDULER", regel 657
ORA-06512: at line1


From the view DBA _SCHEDULER_RUNNING_JOBS
we can see that the job is in a running state.

Changes

It looks that the database was bounced in hard mode, like shutdown abort,
while jobs are still in running status. When a shutdown abort occurs, any still running jobs are killed.
When the database comes back, the job coordinator performs a recovery process on those
jobs that are still listed as running, marks them as failed and finally restarts them when the restartable attribute
of the job is set to true.

In some cases when the job coordinator tries to recover these jobs, it can be kicked out an leaving some jobs in a running state even when there is no job slave associated to this job.

When this happens then it is not possible to drop the job, even with the force option.




Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms