Generate a script to unbreak / break Oracle jobs

--
-- Generate a script to unbreak jobs for a schema
-- Replace USERNAME with schema owner and PASSWORD with schema owner's password
--
-- To break the jobs replace:
-- spool unbreak_jobs.sql -> spool break_jobs.sql
-- broken=>FALSE -> broken=>TRUE 
-- where broken = 'Y' -> where broken = 'N'
--

set echo off verify off feedback off head off pagesize 0

spool unbreak_jobs.sql

select 'connect USERNAME/PASSWORD'
from dual
/

select 'set echo on verify on feedback on head on'
from dual
/

select 'spool log.unbreak_jobs'
from dual
/

select 'execute dbms_job.broken(job=>' || job || ', broken=>FALSE);' 
from dba_jobs
where broken = 'Y'
and schema_user = 'USERNAME'
/

select 'spool off'
from dual
/

select 'exit;'
from dual
/

spool off

exit;