How to Query Jobs in DBA_SCHEDULER_JOBS in Oracle

Managing scheduled jobs in Oracle is crucial for automating tasks like gathering statistics. In this post, I’ll outline the steps I followed to query jobs in DBA_SCHEDULER_JOBS, check their execution schedule, and explore the content of a specific procedure associated with a job.

Step 1: Query DBA_SCHEDULER_JOBS

To view all scheduled jobs in the database, run the following query:

SELECT job_name, program_name, enabled, state
FROM dba_scheduler_jobs;

This query returns a list of all scheduled jobs, including their names, associated programs, whether they are enabled, and their current state.

Step 2: Filter Jobs Related to Statistics Gathering

Next, I was interested in identifying jobs that gather statistics. I refined the query to find relevant jobs:

SELECT job_name, program_name, enabled, state
FROM dba_scheduler_jobs
WHERE job_name LIKE '%STATS%';

The result displayed several jobs, including one named ABC_GATHER_STATS_FULL, which is responsible for collecting database statistics.

Step 3: Check Job Content

To gain a better understanding of what the job ABC_GATHER_STATS_FULL does, I queried its content:

SELECT job_name, job_type, job_action
FROM dba_scheduler_jobs
WHERE job_name = 'ABC_GATHER_STATS_FULL';

This revealed that the job is of type STORED_PROCEDURE and executes the procedure called ABC_GET_STATS.

Step 4: Analyze the Procedure Code

To see the actions performed by this procedure, I queried the DBA_SOURCE table:

SELECT text
FROM dba_source
WHERE name = 'ABC_GET_STATS'
AND type = 'PROCEDURE';

The result showed that the procedure gathers statistics on fixed objects, dictionary statistics, and database statistics using functions from the DBMS_STATS package.

Step 5: Discover the Job Schedule

Finally, to find out when the job ABC_GATHER_STATS_FULL is scheduled to run, I used the following query:

SELECT job_name, next_run_date, start_date, repeat_interval
FROM dba_scheduler_jobs
WHERE job_name = 'ABC_GATHER_STATS_FULL';

This query provided the next execution time and the repeat interval, giving me insights into the job’s scheduling.

Conclusion

Querying jobs in DBA_SCHEDULER_JOBS is an essential practice for Oracle database administration. By following the steps outlined in this post, you can identify, analyze, and understand the scheduling of critical jobs, such as statistics gathering, that directly impact database performance. Always monitor scheduled jobs to ensure they function as expected!