Find SQL Statements behind Oracle Error "ORA-00060 Deadlock Detected"

===========================================
General
===========================================
Application is reporting Deadlock.
How to find the offending SQL Statements?

===========================================
Steps
===========================================

A. Check alert.log

B. Check trace file

C. Get the full SQL Text from DB

===========================================

A. Check alert.log

===========================================

cd $ORACLE_BASE/diag/rdbms/orainst/orainst/trace/

less alert_orainst.log

Tue Feb 09 20:58:42 2016

ORA-00060: Deadlock detected. More info in file /software/oracle/diag/rdbms/orainst/orainst/trace/orainst_ora_1666.trc

===========================================

B. Check the trace file

===========================================

less /software/oracle/diag/rdbms/orainst/orainst/trace/orainst_ora_1666.trc

Trace file /software/oracle/diag/rdbms/orainst/orainst/trace/orainst_ora_1666.trc

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

ORACLE_HOME = /software/oracle/111

System name:    Linux

Node name:      hostname

Release:        2.6.18-274.el5

Version:        #1 SMP Fri Jul 8 17:36:59 EDT 2011

Machine:        x86_64

Instance name: orainst

Redo thread mounted by this instance: 1

Oracle process number: 146

Unix process pid: 1666, image: oracle@hostname

*** 2016-02-09 20:58:42.290

*** SESSION ID:(503.44168) 2016-02-09 20:58:42.290

*** CLIENT ID:() 2016-02-09 20:58:42.290

*** SERVICE NAME:(SYS$USERS) 2016-02-09 20:58:42.290

*** MODULE NAME:(JDBC Thin Client) 2016-02-09 20:58:42.290

*** ACTION NAME:() 2016-02-09 20:58:42.290

*** TRACE FILE RECREATED AFTER BEING REMOVED ***

*** 2016-02-09 20:58:42.290

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-00030012-0006f5ba       146     503     X             91     572           X

TX-00070003-00070332        91     572     X            146     503           X

session 503: DID 0001-0092-000019CC     session 572: DID 0001-005B-0000010B 

session 572: DID 0001-005B-0000010B     session 503: DID 0001-0092-000019CC 

rows waited on:

  Session 503: obj - rowid = 00003940 - AAAViGAALAADe0xAAf

  (dictionary objn - 14656, file - 11, block - 912689, slot - 31)

  Session 572: obj - rowid = 00003942 - AAAViIAAEAAFGiJAAb

  (dictionary objn - 14658, file - 4, block - 1337481, slot - 27)

----- Information for the OTHER waiting sessions -----

Session 572:

  sid: 572 ser: 48 audsid: 33640125 user: 44/MY_SCHEMA flags: 0x41

  pid: 91 O/S info: user: oracle, term: UNKNOWN, ospid: 28338

    image: oracle@hostname

  client details:

    O/S info: user: iu, term: unknown, ospid: 1234

    machine: hostname program: JDBC Thin Client

    application name: JDBC Thin Client, hash value=2546894660

  current SQL:

  

MERGE INTO SFI_CUSTOMER_PROFILE TGT USING (SELECT :B45 AS KEY1, :B44 AS KEY2, :B43 AS KEY3, :B42 AS ATTR1, :B41 AS ATTR2, :B40 AS ATTR3, :B39 AS ATTR4, :B38 AS ATTR5, :B37 AS ATTR6, :B36 AS ATTR7, :B35 AS ATTR8, :B34 AS ATTR9, :B33 AS ATTR10, :B32 AS ATTR11, :B31 AS ATTR12, :B30 AS ATTR13, :B29 AS ATTR14, :B28 AS ATTR15, :B27 AS ATTR16, :B26 AS ATTR17, :B25 AS ATTR18, :B24 AS ATTR19, :B23 AS ATTR20, :B22 AS ATTR21, :B21 AS ATTR22, :B20 AS ATTR23, :B19 AS ATTR24, :B18 AS ATTR25, :B17 AS ATTR26, :B16 AS ATTR27, :B15 AS ATTR28, :B14 AS ATTR29, :B13 AS ATTR30, :B12 AS ATTR31, :B11 AS ATTR32, :B10 AS ATTR33, :B9 AS ATTR34, :B8 AS ATTR35, :B7 AS ATTR36, :B6 AS ATTR37, :B5 AS ATTR38, :B4 AS ATTR39, :B3 AS ATTR40, :B2 AS TS_LAST_MODIFIED, :B1 AS MSISDN FROM DUAL) SRC ON (SRC.KEY1 = TGT.KEY1) WHEN

----- End of information for the OTHER waiting sessions -----

Information for THIS session:

----- Current SQL Statement for this session (sql_id=cyn6n1hvduxmh) -----

MERGE into SFI_CUSTOMER_PROFILE o using (select :1  KEY1 from dual) o1 on (o.KEY1 = o1.KEY1) when matched then update  SET IMSI=:2 , ATTR1=:3 , ATTR2=:4 ,ATTR3=:5 , ATTR4=:6 , ATTR5=:7 , ATTR6=:8 , ATTR7=:9 , ATTR8=:10 ,  ATTR9=:11 , ATTR10=:12 , ATTR11=:13 , ATTR12=:14 , ATTR13=:15 , ATTR14=:16 , ATTR15=:17 , ATTR16=:18 , ATTR17=:19 , ATTR18=:20 ,  ATTR19=:21 , ATTR20=:22 , ATTR21=:23 , ATTR22=:24 , ATTR23=:25 ,  ATTR24=:26 , ATTR25=:27 , ATTR26=:28 , ATTR27=:29 , ATTR28=:30 ,  ATTR29=:31 , ATTR30=:32 , ATTR31=:33 , ATTR32=:34 , ATTR33=:35 ,  ATTR34=:36 , ATTR35=:37 , ATTR36=:38 , ATTR37=:39 , ATTR38=:40 ,  ATTR39=:41 , ATTR40=:42 ,TS_LAST_MODIFIED=sysdate, KEY2=:43 , KEY3=:44 , MSISDN=:45 , ATTR41=:46 , ATTR42=:47 , ATTR43=:48 , ATTR44=:49 , ATTR45=:50 ,  ATTR46=:51 , ATTR47=:52 , ATTR48=:53 , ATTR49=:54 , ATTR50=:55 ,  ATTR51=:56 , ATTR52=:57 , ATTR53=:58 , ATTR54=:59 , ATTR55=:60 ,  ATTR56=:61 , ATTR57=:62 , ATTR58=:63 , ATTR59=:64 , ATTR60=:65 ,  ATTR61=:66 , ATTR62=:67 , ATTR63=:68 , ATTR64=:69 , ATTR65=:70 ,  ATTR66=:71 , ATTR67=:72 , ATTR68=:73 , ATTR69=:74 , ATTR70=:75 ,  ATTR71=:76 , ATTR72=:77 , ATTR73=:78 , ATTR74=:79 , ATTR75=:80 ,  ATTR76=:81 , ATTR77=:82 , ATTR78=:83 , ATTR79=:84 , ATTR80=:85 ,  TS_LAST_TRY_FETCH=:86 , EI_STATUS_CODE_ID=:87   when not matched then  Insert (o.KEY1, o.IMSI, o.ATTR1, o.ATTR2, o.ATTR3, o.ATTR4,o.ATTR5,o.ATTR6,o.ATTR7,o.ATTR8,  o.ATTR9,o.ATTR10,o.ATTR11,o.ATTR12,o.ATTR13, o.ATTR14,o.ATTR15,o.ATTR16,o.ATTR17,o.ATTR18, o.ATTR19,o.ATTR20,o.ATTR21,o.ATTR22,o.ATTR23, o.ATTR24,o.ATTR25,o.ATTR26,o.ATTR27,o.ATTR28, o.ATTR29,o.ATTR30,o.ATTR31,o.ATTR32,o.ATTR33, o.ATTR34,o.ATTR35,o.ATTR36,o.ATTR37,o.ATTR38,  o.ATTR39,o.ATTR40,o.TS_LAST_MODIFIED,o.KEY2,o.KEY3, o.MSISDN, o.ATTR41,o.ATTR42,o.ATTR43,o.ATTR44,o.ATTR45,  o.ATTR46,o.ATTR47,o.ATTR48,o.ATTR49,o.ATTR50, o.ATTR51,o.ATTR52,o.ATTR53,o.ATTR54,o.ATTR55, o.ATTR56,o.ATTR57,o.ATTR58,o.ATTR59,o.ATTR60, o.ATTR61,o.ATTR62,o.ATTR63,o.ATTR64,o.ATTR65, o.ATTR66,o.ATTR67,o.ATTR68,o.ATTR69,o.ATTR70, o.ATTR71,o.ATTR72,o.ATTR73,o.ATTR74,o.ATTR75,  o.ATTR76,o.ATTR77,o.ATTR78,o.ATTR79,o.ATTR80, o.TS_LAST_TRY_FETCH,o.EI_STATUS_CODE_ID) Values (:88 ,:89 ,:90 ,:91 ,:92 ,:93 ,:94 ,:95 ,:96 ,:97 ,:98 ,:99 ,:100 ,:101 ,:102 ,:103 ,:104 ,:105 ,:106 ,:107 ,:108 ,:109 ,:110 ,:111 ,:112 ,:113 ,:114 ,:115 ,:116 ,:117 ,:118 ,:119 ,:120 ,:121 ,:122 ,:123 ,:124 ,:125 ,:126 ,:127 ,:128 ,:129 ,sysdate,:130 ,:131 ,:132 ,:133 ,:134 ,:135 ,:136 ,:137 ,:138 ,:139 ,:140 ,:141 ,:142 ,:143 ,:144 ,:145 ,:146 ,:147 ,:148 ,:149 ,:150 ,:151 ,:152 ,:153 ,:154 ,:155 ,:156 ,:157 ,:158 ,:159 ,:160 ,:161 ,:162 ,:163 ,:164 ,:165 ,:166 ,:167 ,:168 ,:169 ,:170 ,:171 ,:172 ,:173 ,:174 )

===========================================

C. Get the full SQL Text from DB

===========================================

Get the sql_id

SELECT SID, SQL_ID FROM V$SESSION WHERE SID IN (503, 572);

or

SELECT sql_id, SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE 'MERGE into SFI_CUSTOMER_PROFILE o using (select :1  KEY1 from dual) o1 on (o.KEY1 = o1.KEY1) when matched then update%';

Using sql_id, get the full text

SELECT TO_CHAR(SQL_FULLTEXT) FROM V$SQL WHERE SQL_ID = 'cyn6n1hvduxmh';