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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
awking00Information Technology SpecialistCommented:
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.