⏰ Module 12: Oracle Scheduler & Jobs

-- Create a named schedule (every day at 2 AM)
BEGIN
  DBMS_SCHEDULER.CREATE_SCHEDULE(
    schedule_name => 'daily_2am',
    start_date => TRUNC(SYSDATE) + 2/24,
    repeat_interval => 'FREQ=DAILY;BYHOUR=2',
    comments => 'Daily backup schedule'
  );
END;
/

-- More schedule examples
-- Every Monday at 3 AM
FREQ=WEEKLY;BYDAY=MON;BYHOUR=3

-- Every 6 hours
FREQ=DAILY;INTERVAL=6

-- 1st and 15th of month at midnight
FREQ=MONTHLY;BYMONTHDAY=1,15;BYHOUR=0

-- Create a job that runs a procedure
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name => 'backup_job',
    job_type => 'STORED_PROCEDURE',
    job_action => 'SYS.backup_database',
    schedule_name => 'daily_2am',
    enabled => TRUE,
    comments => 'Daily database backup'
  );
END;
/

-- Create inline job with SQL
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name => 'cleanup_logs',
    job_type => 'PLSQL_BLOCK',
    job_action => 'BEGIN DELETE FROM logs WHERE log_date < TRUNC(SYSDATE) - 30; COMMIT; END;',
    repeat_interval => 'FREQ=DAILY;BYHOUR=1',
    enabled => TRUE
  );
END;
/

-- Create chain for sequential jobs
BEGIN
  -- Create chain
  DBMS_SCHEDULER.CREATE_CHAIN(
    chain_name => 'daily_maintenance',
    rule_set_name => 'daily_maintenance_ruleset',
    evaluation_interval => 'FREQ=MINUTELY;INTERVAL=1',
    comments => 'Daily maintenance sequence'
  );
  
  -- Create chain rules (dependencies)
  DBMS_SCHEDULER.DEFINE_CHAIN_RULE(
    chain_name => 'daily_maintenance',
    rule_name => 'analyze_tables_first',
    condition => 'TRUE',
    action => 'START_STEPS',
    step_name => 'analyze_step'
  );
  
  DBMS_SCHEDULER.DEFINE_CHAIN_RULE(
    chain_name => 'daily_maintenance',
    rule_name => 'backup_after_analyze',
    condition => 'analyze_step = COMPLETED',
    action => 'START_STEPS',
    step_name => 'backup_step'
  );
END;
/

-- Enable job
BEGIN
  DBMS_SCHEDULER.ENABLE('backup_job');
END;
/

-- Disable job
BEGIN
  DBMS_SCHEDULER.DISABLE('backup_job');
END;
/

-- Run job immediately
BEGIN
  DBMS_SCHEDULER.RUN_JOB('backup_job');
END;
/

-- Stop a running job
BEGIN
  DBMS_SCHEDULER.STOP_JOB('backup_job');
END;
/

-- Drop job
BEGIN
  DBMS_SCHEDULER.DROP_JOB('backup_job', force => TRUE);
END;
/

-- View all jobs
SELECT job_name, job_type, enabled, state
FROM user_scheduler_jobs;

-- View job execution history
SELECT job_name, log_id, status, run_duration
FROM user_scheduler_job_log
ORDER BY log_date DESC;

-- View running jobs
SELECT job_name, state, cpu_used
FROM user_scheduler_running_jobs;

-- View schedules
SELECT schedule_name, repeat_interval, enabled
FROM user_scheduler_schedules;