ADVERTISEMENT

Disable Oracle Scheduler Jobs: Job Queue, Memory, SPFILE, RAC, Non-SYS Jobs

Oracle Scheduler automates job execution. Sometimes, you need to disable jobs temporarily or permanently — for maintenance, troubleshooting, or performance reasons. This guide covers disabling job queue processes via MEMORY, SPFILE, RAC, and selectively disabling non-SYS jobs.

1. Disable Job Queue Processes in MEMORY (Temporary)

ALTER SYSTEM SET job_queue_processes = 0 SCOPE = MEMORY;
  • Effective immediately on current instance only
  • Resets on instance restart

2. Disable Job Queue Processes in SPFILE + MEMORY (Persistent)

ALTER SYSTEM SET job_queue_processes = 0 SCOPE = BOTH;
  • Changes saved to SPFILE
  • Survives instance restarts

3. Disable Job Queue Processes in RAC (All Instances)

ALTER SYSTEM SET job_queue_processes = 0 SCOPE = BOTH SID='*';
  • Applies change to all RAC instances
  • Ensures cluster-wide consistency

4. Disable Non-SYS Scheduler Jobs Only

Generate disable commands for all non-SYS jobs:

SET LINESIZE 123 PAGESIZE 234
SET HEADING OFF
SPOOL job_disable.sql
SELECT 'EXECUTE DBMS_SCHEDULER.DISABLE(''' || owner || '.' || job_name || ''');'
FROM dba_scheduler_jobs
WHERE owner != 'SYS';
SPOOL OFF;
  • Runs query to create a script disabling all non-SYS jobs
  • Execute generated job_disable.sql script to disable jobs

Notes

  • job_queue_processes = 0 stops all scheduler jobs
  • Use MEMORY scope for temporary changes
  • Use SPFILE scope for persistent changes
  • RAC requires SID=’*’ to target all nodes
  • Disabling non-SYS jobs avoids impacting Oracle internal jobs
  • Always check job status with: SELECT owner, job_name, enabled FROM dba_scheduler_jobs ORDER BY owner;

Summary

  • Temporary disable: MEMORY scope
  • Permanent disable: SPFILE (or BOTH) scope
  • RAC environment: SID=’*’ to apply cluster-wide
  • Selective disable: Generate disable commands for non-SYS jobs

ADVERTISEMENT