DBMS_SCHEDULER Jobs, Programs, Chains, Credentials, Windows, and Operational Observability

Learning outcomes

ServiceHub's nightly reconciliation is launched from one application server's operating-system cron. During a deployment the host is replaced, the job silently disappears, and nobody notices for two days. Moving appropriate automation into Oracle Scheduler gives the database a schema object, schedule, owner, run history, failure status, and dependency model—but Scheduler is not “cron inside Oracle.”

1. Scheduler objects separate what, when, and dependency

A local database job runs as its database job owner. Oracle documents that a named credential is ignored for an ordinary local database job; credentials are for external/remote authentication.

2. The action must be idempotent before the schedule is clever

Retries, crash recovery, operator reruns, deployment races, and manual execution can all cause an action to be invoked more than once over its lifetime. Make the business action idempotent through keys/state transitions/merge semantics rather than assuming “Scheduler runs exactly once.”

CREATE OR REPLACE PROCEDURE servicehub_scheduler_tickAUTHID DEFINERISBEGIN  MERGE INTO servicehub_scheduler_log d  USING (SELECT TRUNC(SYSTIMESTAMP,'MI') AS bucket_ts FROM dual) s  ON (d.bucket_ts=s.bucket_ts)  WHEN NOT MATCHED THEN    INSERT(bucket_ts,logged_at,message)    VALUES(s.bucket_ts,SYSTIMESTAMP,'scheduler tick');  COMMIT; -- This job owns its transaction boundary by design.  DBMS_OUTPUT.PUT_LINE('tick complete');END;/

Unlike an API procedure called inside a user transaction, this background job owns its own unit of work, so an explicit commit can be appropriate. Document that ownership instead of applying a blanket “packages never commit” rule.

3. Create disabled, inspect, then enable

BEGIN  DBMS_SCHEDULER.CREATE_JOB(    job_name        => 'SH12_LOCAL_TICK_JOB',    job_type        => 'STORED_PROCEDURE',    job_action      => 'SERVICEHUB_SCHEDULER_TICK',    start_date      => SYSTIMESTAMP,    repeat_interval => 'FREQ=MINUTELY;INTERVAL=5',    enabled         => FALSE,    auto_drop       => FALSE,    comments        => 'Chapter 12 local job lab'  );  DBMS_SCHEDULER.SET_ATTRIBUTE(    'SH12_LOCAL_TICK_JOB','logging_level',DBMS_SCHEDULER.LOGGING_RUNS  );  DBMS_SCHEDULER.SET_ATTRIBUTE(    'SH12_LOCAL_TICK_JOB','store_output',TRUE  );END;/

SELECT job_name,job_type,job_action,state,enabled,       repeat_interval,next_run_date,auto_drop,store_outputFROM user_scheduler_jobsWHERE job_name='SH12_LOCAL_TICK_JOB';

Jobs are created disabled by default unless you explicitly enable them. Validate arguments, privileges, container, time zone, and rollback/disable path before activation.

4. RUN_JOB has two useful test modes

RUN_JOB(..., use_current_session=>TRUE) runs synchronously in your current session and returns errors directly, but Oracle documents that it does not update normal job-run state/history. With FALSE, Scheduler workers execute asynchronously and the run is reflected in Scheduler job/log views.

BEGIN  DBMS_SCHEDULER.RUN_JOB(    'SH12_LOCAL_TICK_JOB',    use_current_session => TRUE  );END;/BEGIN  DBMS_SCHEDULER.RUN_JOB(    'SH12_LOCAL_TICK_JOB',    use_current_session => FALSE  );END;/-- Requery until the asynchronous run appears.SELECT log_id,job_name,status,actual_start_date,run_duration,       cpu_used,errors,output,additional_infoFROM user_scheduler_job_run_detailsWHERE job_name='SH12_LOCAL_TICK_JOB'ORDER BY log_id DESCFETCH FIRST 5 ROWS ONLY;

5. Time zones and overlap are correctness rules

Scheduler calendaring evaluates repeat intervals into timestamps. Use a region time-zone name in start_date for civil-time schedules that must follow daylight-saving rules rather than a fixed offset. Verify the Scheduler default time zone when start dates are omitted.

For one normally scheduled repeating job, Oracle does not start a new scheduled instance while the current instance is still running. That does not eliminate overlap across two different jobs, manual current-session runs, or multiple systems performing the same business task. If overlap would corrupt data, enforce an application-level/database locking or state-transition policy as well.

SELECT SYS_CONTEXT('USERENV','CON_NAME') AS con_name,       DBMS_SCHEDULER.STIME AS scheduler_timeFROM dual;SELECT name,value,ispdb_modifiableFROM v$parameterWHERE name='job_queue_processes';

In a PDB, JOB_QUEUE_PROCESSES=0 prevents Scheduler/DBMS_JOB work in that PDB. The root value can also disable jobs for all PDBs. Do not change this parameter from a single failed job without diagnosing the container and worker state.

6. Programs and chains make dependencies explicit

A named program makes a reusable action/argument contract. A chain is a set of steps plus rules such as “start validate,” “after validate succeeds start publish,” and “end when publish succeeds.” This is preferable to hiding a workflow dependency inside sleeps or one giant procedural job.

BEGIN  DBMS_SCHEDULER.CREATE_PROGRAM(    program_name   => 'SH12_VALIDATE_PROGRAM',    program_type   => 'STORED_PROCEDURE',    program_action => 'SERVICEHUB_VALIDATE_BATCH',    number_of_arguments => 0,    enabled        => TRUE  );  DBMS_SCHEDULER.CREATE_CHAIN('SH12_RECON_CHAIN');  DBMS_SCHEDULER.DEFINE_CHAIN_STEP(    'SH12_RECON_CHAIN','VALIDATE','SH12_VALIDATE_PROGRAM'  );  DBMS_SCHEDULER.DEFINE_CHAIN_RULE(    'SH12_RECON_CHAIN','TRUE','START VALIDATE'  );  DBMS_SCHEDULER.DEFINE_CHAIN_RULE(    'SH12_RECON_CHAIN','VALIDATE SUCCEEDED','END 0'  );  DBMS_SCHEDULER.ENABLE('SH12_RECON_CHAIN');END;/

This is a design pattern, not part of the minimum lab cleanup below. Chains have additional privileges and lifecycle semantics; use them when dependencies justify the extra object model.

7. Credentials and external/remote jobs are a separate security/topology tier

Current Oracle uses DBMS_CREDENTIAL credential objects for external jobs, remote jobs, and file watchers. Never hard-code a real OS/database password in course source, Git, Scheduler comments, or job arguments. A local database job does not need an OS credential.

-- Requires CREATE CREDENTIAL and an external/remote job use case.VARIABLE cred_password VARCHAR2(128)-- Set :cred_password interactively/through a secret mechanism; do not commit it.BEGIN  DBMS_CREDENTIAL.CREATE_CREDENTIAL(    credential_name => 'SH12_REMOTE_CRED',    username        => 'svc_scheduler',    password        => :cred_password  );END;/-- Drop/rotate the credential according to your secret lifecycle.

External/remote jobs add OS agents/destinations/networking and privileges such as CREATE EXTERNAL JOB. They are not required for this Free local chapter lab.

8. Deliberate failure and run-history diagnosis

BEGIN  DBMS_SCHEDULER.CREATE_JOB(    job_name   => 'SH12_FAIL_JOB',    job_type   => 'PLSQL_BLOCK',    job_action => q'[BEGIN RAISE_APPLICATION_ERROR(-20312,'planned scheduler failure'); END;]',    enabled    => FALSE,    auto_drop  => FALSE  );  DBMS_SCHEDULER.RUN_JOB(    'SH12_FAIL_JOB',    use_current_session => FALSE  );END;/SELECT log_id,status,error#,errors,additional_infoFROM user_scheduler_job_run_detailsWHERE job_name='SH12_FAIL_JOB'ORDER BY log_id DESCFETCH FIRST 3 ROWS ONLY;

The database job log is evidence of Scheduler execution. A local job's SLAVE_PID is worker-process metadata; it is not proof that a separate OS cron/systemd task exists.

9. Reproducible cleanup and operational checklist

BEGIN EXECUTE IMMEDIATE 'DROP TABLE servicehub_scheduler_log PURGE';EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;/CREATE TABLE servicehub_scheduler_log(  bucket_ts TIMESTAMP PRIMARY KEY,  logged_at TIMESTAMP WITH TIME ZONE NOT NULL,  message VARCHAR2(100) NOT NULL);-- Create SERVICEHUB_SCHEDULER_TICK from section 2, then create/run jobs.BEGIN  DBMS_SCHEDULER.DISABLE('SH12_LOCAL_TICK_JOB',force=>TRUE);EXCEPTION WHEN OTHERS THEN NULL; END;/BEGIN DBMS_SCHEDULER.DROP_JOB('SH12_LOCAL_TICK_JOB',force=>TRUE);EXCEPTION WHEN OTHERS THEN NULL; END;/BEGIN DBMS_SCHEDULER.DROP_JOB('SH12_FAIL_JOB',force=>TRUE);EXCEPTION WHEN OTHERS THEN NULL; END;/BEGIN EXECUTE IMMEDIATE 'DROP PROCEDURE servicehub_scheduler_tick'; EXCEPTION WHEN OTHERS THEN NULL; END;/DROP TABLE servicehub_scheduler_log PURGE;

Production designs also need owner/on-call, schedule time zone, expected duration, concurrency/overlap policy, idempotency key/state, max failures/retry policy, notification channel, run-history retention, disable command, and cleanup of obsolete jobs/programs/chains/credentials.

10. Production judgment

Use Scheduler when database-owned background work benefits from database identity, calendars, dependency chains, run history, and PDB-aware operation. Keep external/remote credentials separated from local jobs, and do not confuse Scheduler windows with OS windows. A scheduled job is an application component: version it, monitor it, and test failure/retry behavior.

No paid option, management pack, remote host, restart, or COMPATIBLE change is required for the local database-job lab. Lesson 4 moves from scheduled procedural work into SQL row sources, comparing streaming pipelined functions and shape-changing polymorphic table functions with simpler SQL abstractions.

Authoritative references

• Scheduling Jobs with Oracle Scheduler — jobs, schedules, chains, time zones and credentials
• Oracle Scheduler Concepts — object model, PDB behavior and credentials
• DBMS_SCHEDULER — CREATE_JOB/RUN_JOB and job attributes
• USER_SCHEDULER_JOB_RUN_DETAILS — run-history evidence
• DBMS_CREDENTIAL — external/remote credential objects
• JOB_QUEUE_PROCESSES — CDB/PDB worker control