Important Queries Related to DBMS Scheduler in Oracle

Oracle DBMS Scheduler is a powerful tool for managing scheduled jobs, tasks, and programs within an Oracle database. Here are some important queries related to DBMS_Scheduler jobs in Oracle:

1. List All DBMS Scheduler Jobs:

SELECT job_name, job_type, enabled, state 
FROM dba_scheduler_jobs;

2. List Job Details and Job Actions:

SELECT job_name, job_action 
FROM dba_scheduler_jobs;

3. List Job History:

where job_name='JOB_NAME' order by log_id desc;

4. List Enabled Jobs:

SELECT job_name 
FROM dba_scheduler_jobs
WHERE enabled = 'TRUE';

5. List Disabled Jobs:

SELECT job_name 
FROM dba_scheduler_jobs
WHERE enabled = 'FALSE';

6. List Running Jobs:

SELECT job_name
FROM dba_scheduler_running_jobs;

7. Check the Last Run of a Job:

SELECT job_name, last_start_date, last_run_duration 
FROM dba_scheduler_jobs;

8. List Jobs with Specific Attributes (e.g., job class, credential, destination):

SELECT job_name, job_class, credential_owner, destination_owner
FROM dba_scheduler_jobs;

9. List Job Schedules:

SELECT job_name, start_date, repeat_interval 
FROM dba_scheduler_jobs;

10. List Jobs by Owner:

SELECT owner, job_name, job_action
FROM dba_scheduler_jobs
WHERE owner = 'OWNER';

11. List Jobs by Job Type:

SELECT job_name, job_type
FROM dba_scheduler_jobs
WHERE job_type = 'PLSQL_BLOCK';

12. List Jobs with a Specific Program:

SELECT job_name, program_name
FROM dba_scheduler_jobs
WHERE program_name = 'YOUR_PROGRAM_NAME';

13. List Jobs with a Specific Program Argument:

FROM dba_scheduler_job_args;

14. List Jobs and Their State (e.g., Scheduled, Disabled,Running):

SELECT job_name, state 
FROM dba_scheduler_jobs;

Below are different job states

Disabled – Job is disabled

Scheduled – Job is scheduled to be executed

Running – Job is currently running

Completed – Job completed, not scheduled to run again

Stopped – Job scheduled to run once and was stopped during its run

Broken – Job is broken and has issues

Failed – Job scheduled to run once and failed

Succeeded – Job scheduled to run once and completed successfully

If you like please follow and comment