Skip to Main Content
  • Questions

  • Dbms_scheduler.stop_job is not working

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, prashant.

Asked: April 13, 2016 - 12:20 pm UTC

Last updated: February 06, 2019 - 1:31 am UTC

Version: 12c

Viewed 10K+ times! This question is

You Asked

Can someone tell me what's wrong with DBMS_Scheduler?

SQL> Exec  Dbms_Scheduler.stop_job('US_ALERT',true);
begin Dbms_Scheduler.stop_job('US_ALERT',true); end;
ORA-27466: internal scheduler error: 1870
ORA-06512: at "SYS.DBMS_ISCHED", line 227
ORA-06512: at "SYS.DBMS_SCHEDULER", line 674
ORA-06512: at line 1




Database : Oracle 12c

and Connor said...

Can you try this:

exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED','TRUE');
exec dbms_scheduler.stop_job('US_ALERT',true);
exec dbms_scheduler.drop_job('US_ALERT',true);
exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED','FALSE');

and let us know if that works.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

DROP_JOB a production job???

Ammar, June 29, 2016 - 7:15 am UTC

Won't the 3 entry actually erase the definition of a production job? Are we required to backup the definition, drop the job and then recreate it?
Connor McDonald
June 30, 2016 - 1:32 am UTC

True....I assumed you wanted to get rid of it.

You can omit that line if you want.

Impact of Disabling Scheduler

Srihari, January 23, 2019 - 3:49 pm UTC

Hi Connor,

dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED','TRUE');


Does disabling scheduler impacts others jobs currently scheduled? Any precautions to be considered before setting this attribute?

Thanks for your time in advance.

Regards,
Srihari

Connor McDonald
February 06, 2019 - 1:31 am UTC

A job that is currently running right now will finish, but existing or new jobs will run.

So the jobs still stay on the schedule, but they'll never start.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

DBMS_SCHEDULER

More on PL/SQL routine DBMS_SCHEDULER here