Re: Deadlock and ORA-0600 ocurred yesterday

  • From: Mladen Gogala <gogala.mladen@xxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Sat, 24 Mar 2018 20:53:36 -0400

Trust the query for what? Deadlocks leave trace files. Get someone to send you the trace file and the story is over. With the query below, you will get the list of all tables modified within 6 PM and 7 PM today. BTW, a good practice is to flush the monitoring info to the table, using DBMS_STATS call. How will that help you with the deadlock? Another way would be to query ASH (active session history)  and find all sessions with blocking id not null between 6 PM and 7 PM. All you need to locate is a closed graph: A is waiting on B, B is waiting on C and C is waiting on A. Every deadlock is a closed graph. That will give you SQL statements involved and presumably help you to solve the problem. The problem with deadlocks is that they are usually application logic errors. It is not possible to have deadlock is all the applications lock row in the tables in the same order. If all applications first lock table A, then table B and then table C, there will be no deadlocks. However, if some applications are locking tables in different orders, you may encounter a deadlock.

Regards


On 03/24/2018 05:54 PM, Eriovaldo Andrietta wrote:

Hi,

There were a error ORA-0600 yesterday caused by a deadlock.

I don´t have acesss to the alert of the database server.

Can I trust on this query ?

    select table_name,inserts,updates,deletes,truncated,timestamp
    from dba_tab_modifications
    where timestamp > TO_DATE('23/03/2018 18:00:00','DD/MM/YYYY
    HH24:MI_SS')
    and timestamp <  TO_DATE('23/03/2018 19:00:00','DD/MM/YYYY
    HH24:MI_SS')

    With this query I got a list of tables.


Is there another way to know what is the table that were root cause of the error ?

Great
Eriovaldo


--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217

Other related posts: