How to Enable/Disable a Scheduled Job in Oracle

It is not easy task to manually deal with too many jobs. So to overcome with this this scenario oracle database provides advanced job scheduling capabilities through Oracle Scheduler. The DBMS_SCHEDULER package provides a collection of scheduling functions and procedures that are callable from any PL/SQL program.

Using Scheduler, database administrators and application developers can easily control when and where various tasks take place in the database environment. These tasks can be time consuming and complicated, so using the Scheduler can help them to improve the management and planning of these tasks.

To disable a job that has been scheduled with dbms_scheduler, first you need to identify the job_name, job status and other related information.

To check the job status:

SQL> select job_name, owner, enabled from dba_scheduler_jobs;

SQL> select job_name, enabled from DBA_SCHEDULER_JOBS WHERE job_name = 'SCHEMA_MNTC_JOB';

To Disable a job:

SQL> execute dbms_scheduler.disable('owner.job');

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

PL/SQL procedure successfully completed.



BEGIN

  DBMS_SCHEDULER.DISABLE('SCHEMA_MNTC_JOB');

END;

/

To enable job:

SQL> exec dbms_scheduler.enable('SCHEMA_MNTC_JOB');

PL/SQL procedure successfully completed.




BEGIN

  DBMS_SCHEDULER.ENABLE('SCHEMA_MNTC_JOB');

END;

/

Again you can check the job status using below query:

SQL> select job_name, enabled from DBA_SCHEDULER_JOBS WHERE job_name = ‘GATHER_STATS_JOB’;