To enable all DBMS_SCHEDULER jobs across all schemas in an Oracle database, you can run a PL/SQL anonymous block as a user with DBA privileges (such as SYS or a user with MANAGE SCHEDULER / EXECUTE ANY JOB privileges) that queries DBA_SCHEDULER_JOBS and enables each job dynamically.
PL/SQL Script to Enable All Jobs
Run this script in your SQL tool (like SQLDeveloper or SQLPlus):
DECLARE
v_sql VARCHAR2(1000);
BEGIN
FOR r IN (SELECT owner, job_name FROM dba_scheduler_jobs WHERE state = 'DISABLED') LOOP
BEGIN
v_sql := 'DBMS_SCHEDULER.ENABLE(''"' || r.owner || '"."' || r.job_name || '"'')';
EXECUTE IMMEDIATE 'BEGIN ' || v_sql || '; END;';
EXCEPTION
WHEN OTHERS THEN
-- Print error if a specific job fails to enable, then continue
DBMS_OUTPUT.PUT_LINE('Failed to enable job ' || r.owner || '.' || r.job_name || ': ' || SQLERRM);
END;
END LOOP;
END;
/
Key Considerations
- Privileges: You must execute this as a privileged user (e.g.,
SYSorSYSTEM) because ordinary users cannot modify or enable jobs owned by other schemas. - Double Quotes: The script uses double quotes around the owner and job name (
"owner"."jobname") to safely handle case-sensitive names or special characters. - State Filter: The query targets
WHERE state = 'DISABLED'to skip jobs that are already enabled.
