Modifying a job that is running 1440 times a day

Modifying a job that is running 1440 times a day

We have dozens of jobs that are scheduled to run every single minute. Now, you are required to modify one of those jobs. You made the code changes, tested it into a development database. The job is doing exactly what was requested, the client signed off on testing and your are ready to move into a production database.

Literally, the job is running constantly into a  production database. Oracle won’t let you modify while it is running(great huh?). The job has to be disabled to prevent it from running. A million dollar question is; how do you disable a job that is running 1140 times in a day?

Oracle will not allow you to disable the job while it is running. You need to wait till it is done running. When the job is done running, another job will kick on. The cycle continues and you will not find a window to push this change.

OK, I lied regarding million dollar question, let's try to solve the million dollar question, how do you disable a job while it is running? I tried disabling the job using DBMS_SCHEDULER.DISABLE package; Oracle spits out an error: "The job is currently running".  I ran the package multiple time the Oracle error was persistent.

Let’s see how we can accomplish this:
In order to disable this job, first you need to issue a stop command, then disable and work on your change.

 Step 1:
Check all the running jobs:
select * from dba_schedular_running job;
Here, you will see your job running here. If you don't see it, go to Step 3:

Step 2:
–Stopping running jobs
DBMS_SCHEDULER.STOP_JOB ('schema.jobname');
This command will stop the job as soon as it is done running.

Step 3:
–Disabling a Job:
DBMS_SCHEDULER.DISABLE ('schema.jobname');

Step 4:
Vefify that the jobs are disabled:
select * from dba_schedular_running job;
OR
select * from dba_schedular_job where status like 'DISABLED';

Step 5: 
Run your change!

Step 6:
–Enable the job:
DBMS_SCHEDULER.ENABLE ('schema.jobname');

Congratulations!!
You solved a million dollar question today.