Enable / Disable all dbms scheduled jobs in Oracle

Enable / Disable the dbms scheduled jobs

Check the status of DBMS Scheduled Jobs

SELECT JOB_NAME, OWNER, ENABLED FROM DBA_SCHEDULER_JOBS;

Enable the DBMS Scheduler jobs

exec dbms_scheduler.enable('JOB_NAME');

Disable the DBMS Scheduler Jobs

execute dbms_scheduler.disable('job_name');

Enable all the DBMS Scheduler jobs

begin
-- Use cursor to get list of scheduler jobs 
for r in ( select job_name from dba_scheduler_jobs)
loop
dbms_scheduler.enable(name => r.job_name, force => true);
end loop;
end;
/

Disable all the DBMS Scheduler jobs

begin
-- Use cursor to get list of scheduler jobs 
for r in ( select job_name from dba_scheduler_jobs)
loop
dbms_scheduler.disable(name=> r.job_name, force => true);
end loop;
end;
/

If you want to check currently running jobs

select job_name from dba_scheduler_jobs
 where state = 'RUNNING';

This entry was posted in Oracle on by .
Unknown's avatar

About SandeepSingh

Hi, I am working in IT industry with having more than 15 year of experience, worked as an Oracle DBA with a Company and handling different databases like Oracle, SQL Server , DB2 etc Worked as a Development and Database Administrator.

Leave a Reply