Oracle Deadlock / 死锁 处理

• Get the directory of alert log

sqlplus / as sysdba

show parameters udmp

SQL> show parameters dump "

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
background_core_dump                 string      partial
background_dump_dest                 string      /oracle/diag/rdbms/wind/wi
                                                 nd/trace
core_dump_dest                       string      /oracle/diag/rdbms/wind/wi
                                                 nd/cdump
max_dump_file_size                   string      unlimited
shadow_core_dump                     string      partial
user_dump_dest                       string      /oracle/diag/rdbms/wind/wi
                                                 nd/trace

• get alert log

Tue Dec 27 18:18:38 2016
ORA-00060: Deadlock detected. More info in file /oracle/diag/rdbms/wind/wind/trace/wind_ora_17088.trc

• View /oracle/diag/rdbms/wind/wind/trace/wind_ora_17088.trc

e.g.

*** 2017-01-03 10:43:58.438
*** SESSION ID:(481.8263) 2017-01-03 10:43:58.438
*** CLIENT ID:() 2017-01-03 10:43:58.438
*** SERVICE NAME:(SYS$USERS) 2017-01-03 10:43:58.438
*** MODULE NAME:(SQL*Plus) 2017-01-03 10:43:58.438
*** ACTION NAME:() 2017-01-03 10:43:58.438
 
 

*** 2017-01-03 10:43:58.438
DEADLOCK DETECTED ( ORA-00060 )
 
[Transaction Deadlock]
 
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
 
Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-00050019-0001ae1d        27     481     X             28      26           X
TX-000a0016-0001a6e4        28      26     X             27     481           X
 
session 481: DID 0001-001B-000007E2    session 26: DID 0001-001C-0000302B 
session 26: DID 0001-001C-0000302B    session 481: DID 0001-001B-000007E2 
 
Rows waited on:
  Session 481: obj - rowid = 00019C89 - AAAZyJAAGAAACJtAAA
  (dictionary objn - 105609, file - 6, block - 8813, slot - 0)
  Session 26: obj - rowid = 00019C89 - AAAZyJAAGAAACJtAAB
  (dictionary objn - 105609, file - 6, block - 8813, slot - 1)
 
----- Information for the OTHER waiting sessions -----
Session 26:
  sid: 26 ser: 20080 audsid: 9917129 user: 51/GUEST flags: 0x45
  pid: 28 O/S info: user: wcadmin, term: UNKNOWN, ospid: 9016
    image: oracle@enux-wnc-102.ptcts.com (TNS V1-V3)
  client details:
    O/S info: user: wcadmin, term: pts/2, ospid: 9015
    machine: enux-wnc-102.ptcts.com program: sqlplus@enux-wnc-102.ptcts.com (TNS V1-V3)
    application name: SQL*Plus, hash value=3669949024
  current SQL:
  update temp set name='T2-ID1-NewName' where id=1
 
----- End of information for the OTHER waiting sessions -----
 
Information for THIS session:
 
----- Current SQL Statement for this session (sql_id=a4vh6trxrqttj) -----
select * from temp where id=2 for update
===================================================

• Get  more info

select dbms_rowid.rowid_object('AAAZyJAAGAAACJtAAA') from dual;
--105609
select dbms_rowid.rowid_object('AAAZyJAAGAAACJtAAB') from dual;
--105609
SELECT t.owner,t.object_name,t.object_type FROM all_objects t WHERE t.data_object_id = 105609;

OR
SELECT 'AAFyqCAA1AAGPOKAAC', t.owner,t.object_name,t.object_type FROM all_objects t WHERE t.data_object_id = (select dbms_rowid.rowid_object('AAAZyJAAGAAACJtAAA') from dual);