How to View and Monitor Database Jobs in Oracle

Oracle provides powerful job scheduling capabilities through the DBMS_SCHEDULER package. This guide outlines how to list existing jobs, check their execution status, and retrieve job actions.

---

1. List All Scheduled Database Jobs

Use the following query to retrieve details of all scheduled jobs in the database, including their repeat interval, status, and next scheduled run:

SELECT OWNER,
       JOB_NAME,
       REPEAT_INTERVAL,
       ENABLED,
       STATE,
       LAST_START_DATE,
       LAST_RUN_DURATION,
       NEXT_RUN_DATE
FROM DBA_SCHEDULER_JOBS;

---

2. Check the Status of a Specific Job

To view the execution status of a specific job for a given date, execute the below query. Replace DD-MON-YY with the desired date and &JOB_NAME with the job name.

SELECT LOG_ID,
       JOB_NAME,
       STATUS,
       LOG_DATE
FROM DBA_SCHEDULER_JOB_RUN_DETAILS
WHERE TRUNC(LOG_DATE) = 'DD-MON-YY'
  AND JOB_NAME = '&JOB_NAME';

---

3. View the Job Action of a Specific Job

To determine the actual action or procedure executed by a job, use the following SQL query:

SELECT JOB_ACTION
FROM DBA_SCHEDULER_JOBS
WHERE JOB_NAME = 'JOB_NAME';

Replace 'JOB_NAME' with the actual name of your scheduled job.

---

Conclusion:

These queries offer a straightforward way to manage and monitor scheduled database jobs. Regularly reviewing job statuses and actions helps ensure that background operations are running as expected and supports proactive troubleshooting.