Can't Delete Scheduled Job : ORA-27475 or ORA-27478
(Doc ID 1290836.1)
Last updated on JUNE 29, 2021
Applies to:Oracle Database - Standard Edition - Version 18.104.22.168 to 22.214.171.124 [Release 11.2]
Information in this document applies to any platform.
o Database version 126.96.36.199
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.
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.
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!
In this Document