Re: ORA-600 Deadlock Issues

> > Yes. By default, for every deadlock Oracle generates trace file in
> > USER_DUMP_DEST (so, you don't need to explicitly 'alter session set
> > events...').
>
> The manual says so, but I did see versions of oracle which did not write down
> a trace file. If he is not getting a trace file, he may have one of those
> versions. Setting an event is the only way.
Agree in such case.  

> > Also note that hanganalyze command will not be helpful in your
> > situation. Hanganalyze can only diagnose locks or internal Oracle
> > deadlocks.
>
> This is not true:

Are you sure that your first session doesn't receive ORA-60? Probably, you just don't look in first session output. I reproduced you case and ORA-60 was generated (which is expected).

Note that hanganalyze in your case shows lock, not a deadlock. Deadlocks are printed in 'Cycles' section of hanganalyze trace file. Sessions from 'Open Cahins' section are just locks.

> Hanganalyze will go through all the sessions and try to connect the
> addresses that those sessions are waiting on into a graph. If the graph
> is circular, there is a problem. It catches everything. Checked and proven.
I know. I use hanganalyze event from time to time. Actually I just wrote script (http://oracledba.ru/hanganalyze/hanganalyze.py) which generates hanganalyze event and parses generated trace file.

-- 
Egor
http://www.oracledba.ru/orasrp/
Free Oracle Session Resource Profiler
--
http://www.freelists.org/webpage/oracle-l