Database Job Scheduling & Automation: Jobs, Procedures, Schedules, and Workloads

TAKEAWAYS:

• Oracle’s DBMS_SCHEDULER automates repetitive tasks (backups, reports, cleanups) via jobs, programs, and schedules—replace manual scripts with scheduled PL/SQL.
• Jobs execute once or repeatedly; programs define the task (SQL, PL/SQL, or external scripts); schedules control timing (daily, hourly, or event-based).
• Workload management prioritizes critical jobs (e.g., NEPSE’s end-of-day stock data processing) using resource plans and consumer groups.
• Automation pitfalls: Unchecked jobs can overload the database (e.g., Daraz’s abandoned cart reminder emails); always monitor with DBA_SCHEDULER_JOBS and DBA_SCHEDULER_PROGRAMS.
• Real-world tie: Khalti’s fraud detection runs nightly via a scheduled job that flags unusual transactions using DBMS_SCHEDULER and UTL_FILE for log analysis.
• Exam focus: Know the SQL commands to create jobs, attach programs, and set schedules—plus how to troubleshoot failed jobs using Oracle’s diagnostic views.

Core Concepts: Jobs, Programs, and Schedules

Oracle’s DBMS_SCHEDULER replaces older tools like DBMS_JOB (which only supports PL/SQL). It’s a three-layer system:

1. Jobs: The task to execute (e.g., "Backup database at 2 AM").
2. Programs: The code defining how the job runs (SQL, PL/SQL, or external scripts).
3. Schedules: The when/trigger (calendar-based or event-based).

classDiagram
    class Job {
        +NAME
        +ENABLED
        +STATE (RUNNING/STOPPED)
        +CREATED
        +LAST_START_DATE
    }
    class Program {
        <<abstract>>
        +TYPE (SQL_SCRIPT/PLSQL_BLOCK/EXTERNAL_SCRIPT)
        +COMMAND_TEXT
        +NUMBER_OF_ARGUMENTS
    }
    class Schedule {
        +NAME
        +REPEAT_INTERVAL (e.g., "FREQ=DAILY; BYHOUR=2")
        +START_DATE
        +END_DATE
        +DISABLED
    }
    Job "1" --> "1" Program : executes
    Job "1" --> "1" Schedule : triggered by

1. Programs: What Gets Automated

Programs define how a job runs. Three types:

• SQL_SCRIPT: Direct SQL (e.g., DELETE FROM temp_logs WHERE created < SYSDATE-7).
• PLSQL_BLOCK: PL/SQL anonymous blocks or stored procedures.
• EXTERNAL_SCRIPT: Shell scripts (Linux/Windows) or Java stored procedures.

Example: A PLSQL_BLOCK program to archive old transaction logs for eSewa:

BEGIN
   DBMS_OUTPUT.PUT_LINE('Archiving logs older than 30 days...');
   EXECUTE IMMEDIATE 'INSERT INTO archived_logs SELECT * FROM transaction_logs
                      WHERE log_date < SYSDATE-30';
   COMMIT;
END;
/

2. Jobs: Assigning Programs to Tasks

Jobs link programs to schedules. Key attributes:

• ENABLED: TRUE/FALSE (default: FALSE).
• AUTO_DROP: Delete job after execution (useful for one-time tasks).
• JOB_CLASS: Assigns to a consumer group (e.g., BATCH_JOBS for low-priority tasks).

Worked Example: Schedule a daily backup for Ncell’s customer data (run at 3 AM, priority LOW):

BEGIN
   DBMS_SCHEDULER.CREATE_JOB (
      job_name        => 'NCELL_DAILY_BACKUP',
      job_type        => 'SQL_SCRIPT',
      job_action      => 'BACKUP TABLE customers TO ''/backup/cust_backup_&DATE''.dmp''',
      start_date      => SYSTIMESTAMP,
      repeat_interval => 'FREQ=DAILY; BYHOUR=3',
      enabled         => TRUE,
      job_class       => 'BATCH_JOBS'
   );
END;
/

3. Schedules: Controlling Timing

Schedules use iCalendar syntax (like Outlook). Examples:

Real-World Tie: Pathao’s Driver Availability Report Pathao runs a weekly job at midnight to aggregate driver availability data:

BEGIN
   DBMS_SCHEDULER.CREATE_PROGRAM (
      program_name => 'PATHAO_DRIVER_REPORT',
      program_type => 'STORED_PROCEDURE',
      program_action => 'PKG_REPORTS.GENERATE_AVAILABILITY_REPORT',
      enabled      => TRUE
   );

   DBMS_SCHEDULER.CREATE_JOB (
      job_name     => 'WEEKLY_DRIVER_AVAILABILITY',
      program_name => 'PATHAO_DRIVER_REPORT',
      schedule_name => 'WEEKLY_SUNDAY_MIDNIGHT',
      enabled      => TRUE
   );
END;
/

Workload Management: Prioritizing Jobs

Oracle uses Resource Manager to prevent one job from hogging CPU (e.g., a misconfigured backup job slowing down NTC’s billing system). Key components:

1. Consumer Groups: Assign jobs to groups (e.g., OLTP, BATCH, REPORTING).
2. Resource Plans: Define CPU/memory limits per group.
3. Subplans: Further divide plans (e.g., BATCH → LOW_PRIORITY, HIGH_PRIORITY).

stateDiagram-v2
    [*] --> Resource_Plan
    Resource_Plan --> OLTP: 60% CPU
    Resource_Plan --> BATCH: 30% CPU
    Resource_Plan --> REPORTING: 10% CPU
    BATCH --> LOW_PRIORITY: 20% of BATCH
    BATCH --> HIGH_PRIORITY: 10% of BATCH

Example: Limit Daraz’s inventory update job to 20% CPU during peak hours:

-- Create a consumer group for batch jobs
BEGIN
   DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
      consumer_group => 'DARAZ_BATCH',
      comment        => 'Low-priority batch jobs'
   );
   DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
      group_or_subplan => 'DARAZ_BATCH',
      comment          => 'Limit to 20% CPU',
      mgmt_p1          => 20,  -- 20% of plan
      mgmt_p2          => 0
   );
END;
/

Monitoring and Troubleshooting

Use these views to check job status:

Common Issues & Fixes:

Worked Example: Debug a failed job for NEPSE’s stock data aggregation:

-- Check job status
SELECT job_name, state, last_start_date, last_run_duration
FROM DBA_SCHEDULER_JOBS
WHERE job_name = 'NEPSE_STOCK_AGGREGATE';

-- View logs (requires privilege)
SELECT job_name, log_date, status, action
FROM USER_SCHEDULER_JOB_LOG
WHERE job_name = 'NEPSE_STOCK_AGGREGATE'
ORDER BY log_date DESC;

Output:

JOB_NAME               STATE      LAST_START_DATE          LAST_RUN_DURATION
----------------------- --------- ------------------------- ----------------
NEPSE_STOCK_AGGREGATE  FAILED     2023-11-15 03:00:00     00 00:00:45.123456

Fix: The log shows ORA-01017: invalid username/password. The job uses a stored credential—reset it:

BEGIN
   DBMS_CREDENTIAL.CREATE_CREDENTIAL(
      credential_name => 'NEPSE_BACKUP_CRED',
      username        => 'backup_user',
      password        => 'SecurePass123!'
   );
END;
/

Advanced: Chaining Jobs and Error Handling

Use job chaining to run jobs sequentially (e.g., backup → compress → email report). Add error handling with WHEN OTHERS in PL/SQL.

Example: Khalti’s fraud detection pipeline (3-step chain):

-- Step 1: Flag suspicious transactions
BEGIN
   DBMS_SCHEDULER.CREATE_PROGRAM (
      program_name => 'FLAG_SUSPICIOUS_TXNS',
      program_type => 'PLSQL_BLOCK',
      program_action => '
         BEGIN
            UPDATE transactions SET fraud_flag = 1
            WHERE amount > 100000 AND user_id NOT IN (SELECT id FROM trusted_users);
            COMMIT;
         EXCEPTION
            WHEN OTHERS THEN
               INSERT INTO job_errors VALUES (SYSTIMESTAMP, ''FLAG_SUSPICIOUS_TXNS'', SQLERRM);
               RAISE;
         END;'
   );
END;
/

-- Step 2: Generate report (only if Step 1 succeeds)
BEGIN
   DBMS_SCHEDULER.CREATE_JOB (
      job_name     => 'GENERATE_FRAUD_REPORT',
      program_name => 'FLAG_SUSPICIOUS_TXNS',
      schedule_name => 'DAILY_2AM',
      enabled      => TRUE,
      chain        => TRUE,  -- Runs only if previous job succeeds
      job_class    => 'REPORTING'
   );
END;
/

In the Real World

1. eSewa’s Nightly Reconciliation

   • Idea Used: Scheduled PL/SQL job with DBMS_SCHEDULER.
   • How: Runs at midnight to reconcile payments between banks and eSewa’s database. Uses DBMS_JOB (legacy) for critical tasks and DBMS_SCHEDULER for reports.
   • Code Snippet:

     BEGIN
        DBMS_SCHEDULER.CREATE_JOB (
           job_name     => 'ESEWA_RECONCILE_PAYMENTS',
           program_name => 'PKG_RECONCILE.PROCESS_BATCH',
           schedule_name => 'DAILY_MIDNIGHT',
           enabled      => TRUE,
           job_class    => 'CRITICAL_JOBS'
        );
     END;
     

2. Ncell’s Daily Data Aggregation

   • Idea Used: Resource plans + consumer groups.
   • How: Ncell’s billing system runs a daily job to aggregate call logs. The job is assigned to the BATCH_JOBS consumer group, limited to 30% CPU to avoid impacting real-time services.
   • Visual:

     pie
         title Ncell Database Workload Distribution
         "OLTP (60%)" : 60
         "BATCH (30%)" : 30
         "Reporting (10%)" : 10

3. Daraz’s Abandoned Cart Emails

   • Idea Used: Event-based scheduling + external scripts.
   • How: Daraz triggers a job 24 hours after a cart is abandoned to send a reminder email. The job calls a Python script (via EXTERNAL_SCRIPT) to fetch cart data and send emails via SMTP.
   • Command:

     BEGIN
        DBMS_SCHEDULER.CREATE_PROGRAM (
           program_name => 'SEND_ABANDONED_CART_EMAILS',
           program_type => 'EXTERNAL_SCRIPT',
           program_action => '/home/daraz/scripts/send_abandoned_cart_emails.py',
           enabled      => TRUE
        );
     END;
     

Exam Tip

1. SQL Commands Are Key

   • Memorize these 5 commands (they appear in every exam):

     -- Create a program (PL/SQL/SQL/script)
     DBMS_SCHEDULER.CREATE_PROGRAM(...);
     
     -- Create a job (links program + schedule)
     DBMS_SCHEDULER.CREATE_JOB(...);
     
     -- Start/stop a job
     DBMS_SCHEDULER.START_JOB('JOB_NAME');
     DBMS_SCHEDULER.STOP_JOB('JOB_NAME');
     
     -- Check job status
     SELECT * FROM DBA_SCHEDULER_JOBS WHERE job_name = '...';
     
     -- Drop a job
     DBMS_SCHEDULER.DROP_JOB('JOB_NAME');
     

   • Exam Trick: Always include enabled => TRUE in your answers unless asked otherwise.

2. Real-World Scenarios

   • Expect 2-3 marks on applying scheduling to a scenario (e.g., "Design a job for NTC’s nightly billing").
   • Template Answer:

     "I would create a PLSQL_BLOCK program to generate bills, schedule it daily at 1 AM using FREQ=DAILY; BYHOUR=1, assign it to the BATCH_JOBS consumer group, and enable error logging to USER_SCHEDULER_JOB_LOG."

3. Common Pitfalls

   • Forgetting to enable jobs: enabled => FALSE by default!
   • Ignoring resource plans: Always mention job_class in answers.
   • Overcomplicating: Use simple SQL_SCRIPT for basic tasks (e.g., backups).

4. Diagrams in Exams

   • Draw a 3-layer diagram (Job → Program → Schedule) for descriptive questions.
   • For workload management, sketch a pie chart (like the Ncell example above).