Job Management with DBMS_SCHEDULER in Oracle (Creation, Monitoring, and Management)

Oracle's DBMS_SCHEDULER package offers a powerful and flexible solution for automating, managing, and monitoring tasks like data backups, report generation, and more. This article will walk you through job management with DBMS_SCHEDULER, explaining how to enable, disable, monitor, and troubleshoot jobs to ensure your system runs smoothly.
Creating a Job with DBMS_SCHEDULER:
Here’s a simple example of how to create a job that runs a PL/SQL procedure on a regular basis. 
BEGIN
   DBMS_SCHEDULER.create_job (
      job_name        => 'BACKUP_DB_JOB',    -- Name of the job
      job_type        => 'PLSQL_BLOCK',      -- Type of job (PLSQL_BLOCK, EXECUTABLE, etc.)
      job_action      => 'BEGIN BACKUP_DATABASE; END;', -- PL/SQL block to execute
      start_date      => SYSTIMESTAMP,       -- Start time (current timestamp)
      repeat_interval => 'FREQ=DAILY; BYHOUR=0; BYMINUTE=0; BYSECOND=0;', -- Job runs daily at midnight
      enabled         => TRUE,               -- Enable the job immediately after creation
      comments        => 'Daily Backup Job for Database'
   );
END;
/
Explanation of the Script:
    job_name: The name of the job. In this case, the job is called BACKUP_DB_JOB.
    job_type: Specifies the type of job. For this example, we are using PLSQL_BLOCK, which means the job will execute a PL/SQL anonymous block. You can also use EXECUTABLE for shell scripts or STORED_PROCEDURE to call a stored procedure directly.
    job_action: This is the PL/SQL block that will be executed when the job runs. In this case, it calls the BACKUP_DATABASE procedure (which could be a procedure you’ve already created for performing a database backup).
    start_date: Defines when the job should start. SYSTIMESTAMP ensures that the job starts immediately upon creation.
    repeat_interval: This defines how often the job should run. In this example, the job is scheduled to run daily at midnight using the cron-like format FREQ=DAILY; BYHOUR=0; BYMINUTE=0; BYSECOND=0;.
    enabled: This parameter, when set to TRUE, means that the job will be enabled immediately after creation. If set to FALSE, the job will be created in a disabled state.
    comments: An optional comment field to provide additional information about the job.

Once the job is created, we can verify job details using DBA_SCHEDULER_JOBS view:
SELECT job_name, enabled, repeat_interval, start_date FROM dba_scheduler_jobs WHERE job_name = 'BACKUP_DB_JOB';

Job Management:
we can perform various task using DBMS_SCHEDULER package such as ENABLE, DISABLE, STOP_JOB, and several others.
Enabling and Disabling Jobs:
Enable job:
BEGIN
    DBMS_SCHEDULER.enable (NAME => 'RMAN_INC');
END;
/
Disable job:
BEGIN
    DBMS_SCHEDULER.disable (NAME => 'RMAN_INC');
END;
/
Disable job forcefully:
BEGIN
    DBMS_SCHEDULER.disable (NAME => 'RMAN_INC', FORCE => TRUE);
END;
/
Monitoring Jobs:  
Finding Jobs Currently Running:
SELECT job_name, session_id, running_instance, elapsed_time,cpu_used FROM dba_scheduler_running_jobs;
 
Viewing Job History:
SELECT job_name, log_date, status, actual_start_date,run_duration, cpu_used FROM dba_scheduler_job_run_details;
Stopping Running Jobs:
BEGIN
    DBMS_SCHEDULER.stop_job (JOB_NAME => 'RMAN_INC');
END;
/
Stop forcefully:
BEGIN
    DBMS_SCHEDULER.stop_job (JOB_NAME => 'RMAN_INC', FORCE => TRUE);
END;