Scheduler, Jobs and Automation

44.1 DBMS_SCHEDULER

Components: programs, schedules, jobs, job classes, windows (with Resource Manager), chains (workflows with steps and rules), event-based jobs (AQ, file watchers), external jobs (OS scripts with credentials), remote jobs, lightweight jobs, in-memory jobs.

Calendar syntax: FREQ=WEEKLY; BYDAY=MON,WED; BYHOUR=9.

BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'NIGHTLY_STATS',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN DBMS_STATS.GATHER_SCHEMA_STATS(''SHOP''); END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY; BYHOUR=2; BYMINUTE=0',
    enabled         => TRUE,
    comments        => 'Gather SHOP stats nightly');
END;
/
EXEC DBMS_SCHEDULER.RUN_JOB('NIGHTLY_STATS');
EXEC DBMS_SCHEDULER.DISABLE('NIGHTLY_STATS');
EXEC DBMS_SCHEDULER.DROP_JOB('NIGHTLY_STATS');
SELECT job_name, state, last_start_date, next_run_date FROM user_scheduler_jobs;
SELECT job_name, status, actual_start_date, run_duration, additional_info FROM user_scheduler_job_run_details ORDER BY log_id DESC;

44.2 Automated Maintenance Tasks

Automatic optimizer statistics collection, Segment Advisor, SQL Tuning Advisor (auto), and Automatic Indexing (19c+ on Exadata/ADB).

SELECT client_name, status FROM dba_autotask_client;
EXEC DBMS_AUTO_TASK_ADMIN.DISABLE('sql tuning advisor', NULL, NULL);
SELECT * FROM dba_scheduler_windows;

44.3 Advanced Queuing / Transactional Event Queues (TxEventQ)

Persistent, transactional messaging inside the database. Kafka-compatible APIs (23ai). Propagation and notifications.

EXEC DBMS_AQADM.CREATE_TRANSACTIONAL_EVENT_QUEUE('ORDER_EVENTS');
EXEC DBMS_AQADM.START_QUEUE('ORDER_EVENTS');

44.4 Shell and Tool Automation

Cron or Windows Task Scheduler with SQL*Plus/RMAN scripts, OEM jobs, Ansible roles, Terraform for OCI, the OCI CLI, and the REST APIs (ORDS DB API).