Disabling and Enabling Oracle Scheduled Jobs in sqlplus when your GUI is Unavailable!

The first thing you need to know is what jobs are scheduled and enabled. To do this we simply query the dba_scheduler_jobs view as follows:

SELECT owner, job_name, enabled FROM dba_scheduler_jobs;

List Oracle Jobs

Now we know what jobs are present and enabled/ disabled we can use the DBMS_SCHEDULER to change their status. The DBMS_SCHEDULER is a package which provides a collection of scheduling functions and procedures and in our case since we wish to either disable or enable a job, we simply call its Disable or Enable procedure as follows:

begin
DBMS_SCHEDULER.disable (name => 'STS.STS_OVERNIGHT_REFRESH_JOB');
end;

Configure Oracle job

After you have run the PL/SQL batch to disable or enable the desired job, another check of the dba_scheduler_jobs view will prove that the job state is as you expect.

Enjoy!

Advertisements
This entry was posted in Oracle and tagged , , , , , . Bookmark the permalink.