Oracle

Scheduling Jobs in Oracle

Job scheduling should be available by default. For automatic execution to occur, the following must be true:

  • the DBMS_SCHEDULER package is VALID,
  • JOB_QUEUE_PROCESSES > 0 (engine switch),
  • the CJQ0 (Job Queue Coordinator) process is started as needed,
  • the global attribute SCHEDULER_DISABLED is FALSE (Depreceted, but kept for backward compatibility)

DBMS_SCHEDULER

DBMS_SCHEDULER in Oracle is “a package that provides a collection of scheduling functions and procedures that can be called from any PL/SQL program”. It is the PL/SQL API for creating, scheduling, and managing jobs in the database.

This query checks that the DBMS_SCHEDULER package exists and is marked VALID.

SELECT status
FROM dba_objects
WHERE object_type = 'PACKAGE' AND object_name = 'DBMS_SCHEDULER'

JOB_QUEUE_PROCESSES

Automatic job execution is controlled by the initialization parameter JOB_QUEUE_PROCESSES. As documented: “If JOB_QUEUE_PROCESSES is 0, then DBMS_JOB jobs and Oracle Scheduler (DBMS_SCHEDULER) jobs will not run in the database instance”. If not explicitly set, the default value is “Derived. The lesser value of CPU_COUNT * 20 and SESSIONS / 4”.

This query returns the current value of the JOB_QUEUE_PROCESSES initialization parameter.

SELECT TO_NUMBER(value)
FROM v$parameter
WHERE name = 'job_queue_processes'

It can be set by below query:

ALTER SYSTEM SET job_queue_processes = 0 SCOPE=BOTH;   -- disables automatic execution
ALTER SYSTEM SET job_queue_processes = 200 SCOPE=BOTH;  -- enables with 200 job queue processes

CJQ0 (Job Queue Coordinator)

The background process responsible for executing jobs is CJQ0 (Job Queue Coordinator). According to the documentation: “The job queue coordinator process (CJQ0) selects jobs that need to be run from the data dictionary and spawns job queue worker processes (Jnnn) to run the jobs. Oracle Scheduler automatically starts and stops CJQ0 as needed”.

This query counts the running CJQ0 (Job Queue Coordinator) background processes.

SELECT COUNT(*)
FROM v$bgprocess
WHERE paddr <> '00' AND name = 'CJQ0'

Global Attribute 'SCHEDULER_DISABLED'

Global Attribute 'SCHEDULER_DISABLED' - "When a row is returned, the dbms_scheduler is disabled."

This query returns the value of the SCHEDULER_DISABLED global attribute (TRUE/FALSE).

SELECT UPPER(value)
FROM dba_scheduler_global_attribute
WHERE attribute_name = 'SCHEDULER_DISABLED'

If the value is TRUE, make sure to set it to FALSE.

BEGIN DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('SCHEDULER_DISABLED','FALSE'); END;

Note: No longer documented in current Oracle Database manuals. Kept for backward compatibility.
Recommended: Use JOB_QUEUE_PROCESSES = 0 to disable scheduling.