I am not sure that I have understood your English correctly, but I can assure
you that it is better than my Russian! Well done.

Setting events in arbitrary sessions with DBMS_SYSTEM.SET_EV does work, but to
get a named dump such as a processstate dump you need to specify the dump name
using the 5th parameter. For example:
    execute dbms_system.set_ev(:sid, :serial, 4020, 10, 'processstate');

Yes, it would have been better for a self-deadlock just to take a processstate
rather than a systemstate dump, or otherwise just to cut the section for the
relevant process out of the systemstate dump and send that to me instead -- if
for no other reason than to cut down on the size of the mail. My internet
connection is only 56K.

The trace file you sent me looks consistent with the incremental export problem.
From the top of the trace file you can see that the intended owner of the
exclusive library cache lock is at address 80336ce4, and that is a recursive
transaction. The beginning savepoint number (bsn) for that transaction is 97041
which is also when the X lock was requested and the transaction has not even
been allocated to a rollback segment yet. The immediately preceding history was
a lock on the statement INSERT INTO CLIENT_INDEX_HISTORY (...) VALUES (...)
taken at savepoint number 97038, a DML lock on table 35436 taken in SX mode at
savepoint number 97039, and some change in the parent transaction at savepoint
number 97040. Presumably table 35436 is the CLIENT_INDEX_HISTORY table. Could
you please check that with
    select owner, name from dba_objects there object_id = 35436;
Also, are there any after row, or after statement triggers on this table that
could account for the recursive transaction?


-----Original Message-----
Sent: Saturday, 26 August 2000 22:50


I have some questions about deadlocks and I need your advice.
We had terrible situation some days ago. There were many complaints about
deadlocks from one of our customer.
The customer's database is on 8.1.6.0 server, Solaris box. I read
www.ixora.com.au and your wise advice to avoid incremental backup really
helped us!!! Today is already two days with no additional customer's
complaints about deadlocks.

Many many many thanks!!!

Could you answer some questions about tracing of the deadlocks? I bought
your book "Oracle8i Internal Services" some time ago. It's very cool,
thanks. But I couldn't find answers on my following questions there.

We have a quite complex application and I must trace another session rather
than my own. So I can't use 
    alter session set events '4020 trace name processstate forever, level 10';
I tried using the following command
    exec dbms_system.set_ev(SID,SERIAL#, 4020, 10,'');
but unfortunately, it looks like it doesn't work.
No trace files have been generated.
Is there any way to trace deadlocks for particular (not my own) session?

I've traced on the system level by setting the event in the init.ora via
    event = "4020 trace name systemstate forever, level 10"
I've got trace file (see attachment). It probably should contain the only
traced session, not other user sessions were allowed at that time. I see
this is  a self deadlock, and there are no any recursive sessions in the
trace file.

What is the next trace information I must attract particular attention? In
the ixora's Q&A you wrote: " The other strange thing is that the intended
owner of the lock is a transaction state object, not a call state object".
How can I determine this fact from the trace? What another details are more
important to determine a possible cause of the problem?

When we've suggested the cause of problem, we did recompiling for _all_
stored objects (valid or not). It didn't help. The only way we have found is
did full export of the schema and then import. It's very strange. What do
you think about it?

Sorry for my poor English. I hope you will understand my bird language :-).
I'm in the process of studying