Re: Deadlock and ORA-0600 ocurred yesterday

 Eriovaldo,

That's great, but I wonder how can you be sure that you got snaps relevent to this deadlock, because it can be very short and there is a chance that DBA_HIST_ACTIVE_SESS_HISTORY
doesn't contain snapshots with this locks. Moreover, I'd recommend to remove predicate sql_opname not in ('SELECT'), because SELECT FOR UPDATE can cause deadlock too. Btw, it seems you have syssdba privileges, so you can easily "create directory" and read tracefile using bfile. For example (for your dir path and trace filename): create directory TRACE_DIR as '/home/oracle/app/oracle/diag/ rdbms/sgc10pr/sgc10pr/trace/';
select bfilename('TRACE_DIR','sgc10pr_ora_12256726.trc') trc from dual;

Also you haven't provided your oracle version, so just in case if it is Oracle 12.2, you can use special view V$DIAG_TRACE_FILE_CONTENTS: https://antognini.ch/2016/09/sql-trace-in-oracle-database-exadata-express-cloud-service/ For example:
select payload from V$DIAG_TRACE_FILE_CONTENTS where trace_filename = ' sgc10pr_ora_12256726.trc';

On Sun, Mar 25, 2018 at 4:34 AM, Eriovaldo Andrietta <ecandrietta_at_gmail.com> wrote:

> Hello,
>
> I got the sql_id involved :
>
> select *
> from DBA_HIST_ACTIVE_SESS_HISTORY
> where sample_time between TO_DATE('23/03/2018 18:47:00','DD/MM/YYYY
> HH24:MI:SS') and
> TO_DATE('23/03/2018 18:47:35','DD/MM/YYYY
> HH24:MI:SS')
> and sql_opname not in ('SELECT')
> and blocking_session IS NOT NULL;
>
>
> Thanks for all answers.
>
> Eriovaldo
>
>
>
> 2018-03-24 21:53 GMT-03:00 Mladen Gogala <gogala.mladen_at_gmail.com>:
>
>> 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
>>
>>
>

-- 
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org

--
http://www.freelists.org/webpage/oracle-l