-- Oracle database scheduler jobs need to be enabled and disabled as required by business processes.

-- DBA can stop the jobs by using ALTER SYSTEM option.  By setting the JOB_QUEUE_PROCESSES to 0, 
-- the jobs will not execute (DBMS_SCHEDULER jobs and DBA_JOBS).  By this option the state of
-- jobs will stay as "Scheduled"


ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;

-- By setting to a number higher than 0 will automatically allow jobs to execute as scheduled.
-- can be any integer other than 0


ALTER SYSTEM SET JOB_QUEUE_PROCESSES=100; 

-- Single job enablement and disablement can be done by simple command/script execution

-- Enable job

exec DBMS_SCHEDULER.enable('owner.database_job_name');

-- or by a PL/SQL block

BEGIN DBMS_SCHEDULER.enable('owner.database_job_name'); END;
/

-- Disable job

exec DBMS_SCHEDULER.disable('owner.database_job_name'); 

-- or by a PL/SQL block

BEGIN DBMS_SCHEDULER.disable('owner.database_job_name'); END;
/


-- To enable all user jobs following code can be used. To execute the code for other users 
-- commented code can be used.

DECLARE
 CURSOR c_jobs IS
  SELECT job_name
  FROM  dba_scheduler_jobs
  WHERE owner = user;      --owner = UPPER('&i_owner')

BEGIN
 FOR usr_jobs IN c_jobs LOOP
   EXECUTE IMMEDIATE 'BEGIN DBMS_SCHEDULER.enable('''||usr_jobs.job_name||'''); END;';
 END LOOP;
END;
/

-- To disable all user jobs following code can be used.
DECLARE
 CURSOR c_jobs IS
  SELECT job_name
  FROM  dba_scheduler_jobs
  WHERE owner = user;      --owner = UPPER('&i_owner')

BEGIN
 FOR usr_jobs IN c_jobs LOOP
   EXECUTE IMMEDIATE 'BEGIN DBMS_SCHEDULER.disable('''||usr_jobs.job_name||'''); END;';
 END LOOP;
END;
/

-- Note: For the above code to work, the jobs names cannot have blank spaces, such as
-- "LOAD META DATA".  For that type of names, those have to be excluded in the cursor
-- and loaded one by one.  Best practice is to not have gaps in the name.

exec DBMS_SCHEDULER.enable('owner."LOAD META DATA"');


-- DBA_JOBS: Check status

SELECT * FROM  dba_jobs
WHERE  INSTR(what,'MVW') > 0
AND    broken = 'N'           --Y for invalid (broken) jobs
;