Stopping a Running Job
Sometimes, we may find scheduled jobs currently impact other running sessions, we may consider to turn it off right away. Killing the scheduled job sessions may be a solution, but you can stop it more gracefully.
Let's see what scheduled jobs are running in the database.
SQL> column job_name format a50;
SQL> select job_name from dba_scheduler_running_jobs;
JOB_NAME
--------------------------------------------------
ORA$AT_OS_OPT_SY_2981
Then we stop it in the formal way by using STOP_JOB, one of subprograms of package DBMS_SCHEDULER.
Stop a Single Job
Usually, you can stop a scheduler job by passing its JOB_NAME, JOB_DEST_ID or JOB_CLASS_NAME.
SQL> exec sys.dbms_scheduler.stop_job('ORA$AT_OS_OPT_SY_2981');
Stop Multiple Jobs
For stopping multiple jobs, you can pass a list of jobs delimited by comma. Again, they can be JOB_NAME, JOB_DEST_ID, JOB_CLASS_NAME or mixed.
SQL> exec sys.dbms_scheduler.stop_job('ORA$AT_OS_OPT_SY_2981, 4213, SYS.TOP_CONSUMER');
Stop a Job Forcibly
Sometimes, jobs may not be stopped as you wished, you need to forcibly stop it.
SQL> exec sys.dbms_scheduler.stop_job(job_name => 'ORA$AT_OS_OPT_SY_2981', force => true);
PL/SQL procedure successfully completed.
Let's check scheduled jobs again.
SQL> select job_name from dba_scheduler_running_jobs;
no rows selected
There's no running scheduled jobs.
Please note that, we stopped the execution of the job this time, but it will start to execute again at the next scheduled time. This is because we just stopped it, not dropped it.