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

You can create a jobs with the DBMS_SCHEDULER package or with the DBMS_JOB. So if you have created it with the DBMS_SCHEDULER package then you should check the manual pages for this package how to disable a job. The documentation is in PL/SQL Packages and Types Reference. The documentation of all version and features can be found here. If I search for the word "disable" in this document, the second occurrence is this

DISABLE Procedure

Disables a program, job, chain, window, database destination, external destination, file watcher, or group

So follow the link in the document and disable the procedure.

If you are already familiar with the DBMS_SCHEDULER.DISABLE procedure but don't remember its arguments you can query the database for the exact signature of this procedure.

Open a sqlplus sessions and find a description of the DBMS_SCHEDULER package

$ sqlplus user/password@database
SQL> spool desc.out
SQL> desc DBMS_SCHEDULER
     ....
SQL> spool off

in the file desC.out, where the output of the above command is written, look fo the word DISABLE. You will find

PROCEDURE DISABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NAME                           VARCHAR2                IN
 FORCE                          BOOLEAN                 IN     DEFAULT
 COMMIT_SEMANTICS               VARCHAR2                IN     DEFAULT

So to disable your job named MY_JOB execute in sqlplus the command

SQL> begin dbms_scheduler.disable('MY_JOB'); end;
SQL>/

or shorter

SQL> exec dbms_scheduler.disable('MY_JOB')

which means exactly the same.

---

If you have created the job with DBMS_JOB you find the appropriate procedure in the same way. But such jobs can't be disabled, they must be set to broken.

BROKEN Procedure

This procedure sets the broken flag. Broken jobs are never run.

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');

Answer 3

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 4

1. You can create a PL/SQL Procedure that disable your job, with the name in parameter

BEGIN
DBMS_SCHEDULER.DISABLE('name_of_your_job');
END;
/

2. Execute it EXEC dbms_scheduler.disable('name_of_your_job');

I hope this will solve your problem