What would be a typical SQL query to get a list of failed jobs

Document ID : KB000050238
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

What would be a SQL query in Oracle to retrieve FAILED or TERMINATED jobs which run the last 24 hours?

Solution:

You can get such a list with the following query:

select j.job_name,                                                         
c.text,                                                                    
to_char(j.que_status_stamp,'DD/MM/YYYY HH24:MI:SS') "HOUR"                
from autosys.proc_event j, autosys.intcodes c                              
where                                                                      
        j.QUE_STATUS_STAMP>(SYSDATE-1)                                     
        and (j.status=5 or j.status=6)                                     
        and j.status=c.code                                                 
order by 1;