Skip to content
Home » Oracle » How DBMS_SCHEDULER.STOP_JOB

How DBMS_SCHEDULER.STOP_JOB

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.

Leave a Reply

Your email address will not be published. Required fields are marked *