Jobs Oracle monitoring - version 9i

Good afternoon,

Someone would have a query, where I can monitor the jobs that failed in the last 24 hours.

But it must be dba_jobs, because the oracle version is 9i

Thank you very much
Support_38Asked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
You can look at the failures column to get the jobs that failed since last success. last_date is the date of the last successful run.

I'm not sure there is a way to only show jobs that failed in the last 24 hours.

It has been a long time since I messed with DBA_JOBs but maybe you can do some magic with the interval and last_date combined with failures?
0
 
awking00Connect With a Mentor Commented:
Perhaps something like
select job
from dba_jobs
where failures > 0
and last_date > (select max(last_date) from dba_jobs where failures = 0)
and what = 'jobname' ==> optional where condition(s)
0
 
slightwv (䄆 Netminder) Commented:
>>and last_date > (select max(last_date) from dba_jobs where failures = 0)

Not sure you can go against ALL jobs to get failures of a single one.

I'm also guessing they might want jobs that might only run once a week/month but failed this morning?
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Support_38Author Commented:
I have this query, and would need to adapt it to oracle 9

It's possible ?


SELECT
job_log.job_name, job_log.log_date
FROM
dba_scheduler_job_log job_log, (
SELECT
MAX(log_date) max_date, job_name
FROM
dba_scheduler_job_log
GROUP BY
job_name
) last_run
WHERE
job_log.status = 'FAILED' AND
job_log.log_date > sysdate - (? / 1440) AND
last_run.max_date = job_log.log_date;
0
 
NerdsOfTechTechnology ScientistCommented:
 SELECT 
	to_char(log_date, 'DD-MON-YY HH24:MI:SS') TIMESTAMP
	, job_name
	, job_class
	, operation
	, status 
	, SUBSTR(additional_info, 1, 40) ADDITIONAL_INFO
 FROM USER_SCHEDULER_JOB_LOG
 WHERE status = 'FAILED'
 	AND log_date > sysdate - (? / 1440)
 ORDER BY log_date;

Open in new window


For more info on scheduling and other admin queries, see:
https://docs.oracle.com/html/E25494_01/scheduse008.htm
0
 
slightwv (䄆 Netminder) Commented:
*_SCHEDULER_JOB_LOG is for DBMS_SCHEDUILER jobs not DBMS_JOB jobs.

I don't think DBMS_JOB has the same logging views that DBMS_SCEHDULER has.
0
 
NerdsOfTechTechnology ScientistCommented:
0
 
slightwv (䄆 Netminder) Commented:
If you go look at the views that appear to be associated with DBMS_JOB there are two: DBA_JOBS and DBA_JOBS_RUNNING.
1
 
NerdsOfTechTechnology ScientistCommented:
When a job fails, information about the failure is recorded in a trace file and the alert log. Oracle writes message number ORA-12012 and includes the job number of the failed job.
0
 
Support_38Author Commented:
thanks
0
 
slightwv (䄆 Netminder) Commented:
@Support_38,

Can I ask why you awarded points to a post that isn't possible as a solution in 9i?  #a42538977
0
 
NerdsOfTechTechnology ScientistCommented:
Yes, please remove my solution as that is for v11.2
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.