2

Is there an object I can query in 12c to return all ORA-60 Deadlock alerts?

I just want to do a weekly report that outputs the number of deadlocks that have occurred and what schema they occurred on.

My Oracle knowledge is not great, and I have drawn a blank when searching for resources on this online.

Thanks

2

Sure:

select
  ss.value
from  
  v$sysstat ss 
  join v$statname sn using (statistic#)
where
  sn.name = 'enqueue deadlocks'
;

I did not test it extensively though.

  • This seems to give me a number, but I'm not sure what time period it is for, and I'd like to break it down by schema if possible – Molenpad Dec 13 '18 at 9:33
  • @Molenpad It is the number of deadlocks since instance startup. I am not aware of any semi-reliable method that gives this information by schemas other than analyzing all the trace files created on deadlock occurences. – Balazs Papp Dec 13 '18 at 10:00
1

The deadlocks are reported to the alert log with the error ora-00060 if I remember well, and with X$DBGALERTEXT you should be able to "query" the alert log, based on that you should be able to create a query where you can get the deadlocks occurred during some window (days, weeks, etc) as long as you have the info in the alert log.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.