History of deadlocks in Oracle?

Question

Does oracle keeps any history about deadlocks?

e.g. Can i know which sqls were executed when the deadlock was detected, and oracle exception ORA-00060 (deadlock detected while waiting for resource) is thrown?

Thanks in advance.

Reisman

Answer 1

There's no history built-in to the database, however, when a deadlock occurs, a couple of things happen. First, it gets logged to the alert.log. Second, a trace file is written. So, there's some history recorded there.

The tracefile will contain many useful bits of information, such as: - deadlock graph - SQL that encountered the deadlock - other statements involved in the deadlock

These pieces of information should help you track down the source of the deadlock.

If you have any specific cases or examples, identify the tracefile, and post the pieces I mentioned above, and I'm sure someone will be able to help you understand what's going on.

Hope that helps.

Schoolhouse

Is your deadlock presentation available somewhere online that you could link to? I know at one point it was on the OakTable web site but I can't find it any longer. – Boles

Hey Justin, how are you? The presentation is available on the "new" Oaktable site, at: oaktable.net/contributedfiles At the moment, it's the first file listed. – Schoolhouse