How to disable a job in oracle with dbms_scheduler

Question

I created a job which runs in my database successfully with DBMS_SCHEDULER ,but now I need to disable this job, how can i do this? thanks!

Answer 1

Although the current answers provide a solution to how disable a job, I wanted to go a bit further and explain you how the job is created has an effect on whether the job needs to be disabled in the first place.

I am assuming you are using dbms_scheduler.

Job is created with auto_drop true and enabled true

In this case, once the job is created ( assuming you don't have any start time in the future ) the job executes immediately ( because it is enabled ) and then it is dropped automatically ( auto_drop is true )

SQL> begin
DBMS_SCHEDULER.create_job
        (
                job_name             => 'MY_TEST',
                job_type             => 'PLSQL_BLOCK',
                job_action           => 'begin dbms_lock.sleep(5); end;',
                enabled              =>  TRUE ,
                auto_drop            =>  TRUE
        );
end;
/  2    3    4    5    6    7    8    9   10   11

PL/SQL procedure successfully completed.

SQL>
SQL> exec  dbms_lock.sleep(5); -- waiting 5 seconds 

PL/SQL procedure successfully completed.

SQL> select job_name,job_action from dba_scheduler_jobs where job_name = 'MY_TEST' ;

no rows selected

SQL>

Job is created with auto_drop to false and enabled to true

In this case, the job runs and it disables itself automatically. In this scenario you don't need to do anything to disable it.

SQL> begin
  2  DBMS_SCHEDULER.create_job
  3     (
  4             job_name             => 'MY_TEST',
  5             job_type             => 'PLSQL_BLOCK',
  6             job_action           => 'begin dbms_lock.sleep(5); end;',
  7             enabled              =>  TRUE ,
  8             auto_drop            =>  FALSE
  9     );
 10* end;
 11  /

 PL/SQL procedure successfully completed.

select job_name , state, enabled from dba_scheduler_jobs where job_name = 'MY_TEST' ;

JOB_NAME    STATE           ENABLE
----------------------------------
MY_TEST     SUCCEEDED       FALSE

Therefore, if your job is enabled is because it has a calendar frequency associated to it, so once has executed, it states enabled until the next time it has to execute

Job with frequency

It means that the job was created to executed based on an expression calendar. In this case, the job executes based on the calendar expression associated to it, and remains enabled and in state SCHEDULED.

SQL>  exec dbms_scheduler.drop_job ( job_name => 'MY_TEST' ) ;

PL/SQL procedure successfully completed.

SQL> begin
DBMS_SCHEDULER.create_job
(job_name             => 'MY_TEST',
 job_type             => 'PLSQL_BLOCK',
 job_action           => 'begin dbms_lock.sleep(5); end;',
 enabled              =>  TRUE ,
 start_date           =>  systimestamp ,
 repeat_interval      => 'FREQ=DAILY;BYDAY=MON,TUE,WED,THU,FRI;'
 );
 end;
 / 

 PL/SQL procedure successfully completed.

SQL> select job_name , state, enabled from dba_scheduler_jobs where job_name = 'MY_TEST' ;

JOB_NAME        STATE           ENABLE
--------------------------------------
MY_TEST       SCHEDULED         TRUE

In this case, as it was point out in the other answers:

SQL> exec dbms_scheduler.disable ( 'MY_TEST' ) ;

PL/SQL procedure successfully completed.

SQL> select enabled  from dba_scheduler_jobs where job_name = 'MY_TEST' ;
    
ENABL
-----
FALSE

Summary

• If you want to run a job just once and eliminate it, use the option 1 ( auto_drop and enabled )
• If you want to run a job and leave it there for run it on demand whenever you want, but disabled. Use option 2 ( auto_drop to false and enabled to true )
• Normally you disable jobs that are created with frequency and execute based on some kind of calendar expression.

Obviously, that is just a set of small examples of the many options you have available with dbms_scheduler

Answer 2

Oracle has a good package for schedule jobs.

In your case , you need disable procedure. Here is the detailed information about dbms_scheduler

Simply call this using oracle new query window like this and your job will be disabled:

begin dbms_scheduler.disable('job-name'); end;

Or in command window :

exec dbms_scheduler.disable('SCHEMA_MNTC_JOB');