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

Symptoms

o Database 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: "user.XX_00000_4911" 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 "User.yy_00018_D_YYY" 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 re-startable 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 secondary job associated to this job.

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

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.