All You Need To Know About Oracle Scheduled Jobs

Published: Thu, 10/06/16

Image


Do you get frustrated when dealing with dbms_scheduler jobs?
Do you forget the data dictionary views that can be queried to find information about database jobs?
Do you find yourself looking on the web, site after site, to find the query that answers your question: “Why did job X failed” ?
Do you get sleepy reading the documentation on dbms_scheduler, in search of basic information?


Just like you, I find dbms_scheduler very complex, too complex for the most things I need to do as a DBA.

Questions I need to answer, are usually simple, basic questions. I am sure you have the same ones:


When did job X last run?
Was it successful?
If it failed, what was the error?
Does this job take any arguments?
What instance is the job scheduled to run on?
How do I modify the job?
How do I get the ddl of the job?
Is the job taking longer now? Why?


If I have a GUI tool, I can easily find answers for my questions.I preferred using the GUI for any scheduled job questions, until now.


But …


What do you do, if you do not have a GUI tool available, such as Toad, SQL Developer or other, and the only thing you can use is SQLPlus?


Let me show you what you do!


I’ll walk you through the most important things you need to know about scheduled database jobs.


I will not bore you with theoretical information, and explanation available in user manuals (you can read that before you go to bed).


What you’ll learn today, is precious stuff you can apply right away!


1. Most Used Data Dictionary Views For Database Jobs
2. Basic Information You Need To Know About All The Jobs Scheduled In The Database
3. Detailed Information You Need To Know About A Specific Scheduled Job



1. Most Used Data Dictionary Views For Database Jobs

Did you know too much information can be overwhelming? You need to focus on the information that is important to you.
There are 4 dictionary views I want to query:


  • DBA_SCHEDULER_JOBS
  • DBA_SCHEDULER_JOB_ARGS
  • DBA_SCHEDULER_JOB_RUN_DETAILS
  • DBA_SCHEDULER_RUNNING_JOBS


To make it easier to remember use this mind map (click on it to open the image):



Image


2. Basic Information You Need To Know About All The Jobs Scheduled In The Database


  • what is the job name?
  • who owns the job?
  • is the job enabled?
  • what is the repeat interval, how often does the job run?
  • when did the job last run?
  • is the job scheduled to run in a specific instance?


Script job_info_all.sql gives you the answer:


/*
Script name: job_info_all.sql
 
Example:     @job_info_all.sql 
 
Created By   Diana Robete
Copyright:   @2016 dbaparadise.com
*/
 
 
set linesize 180
col enabled for A7
col args# for 99999
col last_start_date for A44
col stick for A5
 
select owner, job_name, enabled,number_of_arguments as args#, 
instance_stickiness as stick, instance_id, last_start_date 
from dba_scheduler_jobs 
order by enabled desc, owner, job_name;

Sample Output (Click on the image for a better view)

Image


3. Detailed Information You Need To Know About A Specific Scheduled Job

If I need to dig deeper, and find more information about one particular job, I would look for the following:


  • what are the arguments for the job?
  • did the job completed successfully?
  • when and how long did it run?


Script job_info.sql gives you the answer:


/*
Script name: job_info.sql
 
Example:     @job_info.sql JOB_NAME
 
Created By   Diana Robete
Copyright:   @2016 dbaparadise.com
*/
 
set linesize 180
set define '&'
set pagesize 500
set  verify off
set feedback off
 
col enabled for A7
col args# for 99999
col last_start_date for A44
col stick for A5
col job_name for A30
col log_date for A40
col status for A10
col error# for 99999999
col run_duration for A15
col inst for 99999
col additional_info for A60 WORD_WRAPPED
col value for A60
 
define _job=&1
 
Prompt
Prompt #######################################################
Prompt General Information About Job &&_job
Prompt #######################################################
Prompt
 
select owner, job_name, enabled,number_of_arguments as args#, 
       instance_stickiness as stick, instance_id, last_start_date
from dba_scheduler_jobs
where upper(job_name) = upper('%&_job%')
order by enabled desc, owner, job_name;
 
Prompt
Prompt #######################################################
Prompt Job &_job Arguments
Prompt #######################################################
Prompt
 
select owner,job_name,argument_position, argument_name, value
from dba_scheduler_job_args
where upper(job_name) = upper('%&_job%')
order by argument_position;
 
Prompt
Prompt #######################################################
Prompt Job &_job Last 5 Runs
Prompt #######################################################
Prompt
 
select job_name,log_date,status,error#,run_duration,instance_id inst,
       additional_info additional_info
from dba_scheduler_job_run_details
where upper(job_name) = upper('%&_job%')
and rownum<6
order by log_date desc;

Sample Output (Click on the image for a better view)
Image
 
Remember - sharing is caring...

Forward this email to a friend!

 You're the best!
 
 
 
Diana Robete
Sincerely,
Diana Robete
diana@dbaparadise.com