Hi ,
Version 11202
i am getting the following error message is the alert log: DEADLOCK DETECTED ( ORA-00060 )
The strange ting is that the waiting statment is sql statment and not a DML statment.
As far as i know dead lock is happening against update statments and not sql statments.
[Transaction Deadlock]
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
BF-004b3c01-00000000 142 2041 S 35 490 S X
PS-00000001-00000002 35 490 S 142 2041 S X
session 2041: DID 0001-008E-000005CC session 490: DID 0001-0023-00000EB6
session 490: DID 0001-0023-00000EB6 session 2041: DID 0001-008E-000005CC
Rows waited on:
Session 2041: obj - rowid = 00532B47 - AAXDn7ADOAABNqVAAA
(dictionary objn - 5450567, file - 206, block - 318101, slot - 0)
Session 490: no row
----- Information for the OTHER waiting sessions -----
Session 490:
sid: 490 ser: 2296 audsid: 113626716 user: 75/CRM
flags: (0x41) USR/- flags_idl: (0x21) BSY/-/-/DEL/-/-
flags2: (0x40004) -/-
pid: 35 O/S info: user: oracle, term: UNKNOWN, ospid: 15212
image: oracle@thor (P002)
client details:
O/S info: user: noamat, term: PC7986, ospid: 15212
machine: xx\yyy program: oracle@thor (P002)
current SQL: <none>
----- End of information for the OTHER waiting sessions -----
*** 2011-10-11 16:37:30.671
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=3, mask=0x0)
----- Error Stack Dump -----
ORA-00060: deadlock detected while waiting for resource
----- Current SQL Statement for this session (sql_id=182wy82gzs0c6) -----
SELECT
CRM.CM_CUST_DIM_1.INST_PROD_ID,
CRM.CM_CUST_DIM_1.PRODUCT_ID,
CRM.CM_CUST_DIM_1.NAP_CRM_STATUS_DESCR,
bb.date_dim.real_date,
CRM.ESL_DATA_FACT.USER_NAME,
CRM.CM_CUST_DIM_1.CST_BO_NAME,
CRM.CM_CUST_DIM_1.NAP_INT_CUST_NUM
FROM
CRM.CM_CUST_DIM_1,
bb.date_dim,
CRM.ESL_DATA_FACT,
CRM.CM_IP_SERVICE_DIM
WHERE
( CRM.ESL_DATA_FACT.date_id=bb.date_dim.date_id AND CRM.ESL_DATA_FACT.month_seq_num=bb.date_dim.month_seq_num AND bb.date_dim.agg_level = 1 )
AND ( CRM.CM_IP_SERVICE_DIM.int_manui_id=CRM.ESL_DATA_FACT.int_manui_id )
AND ( CRM.CM_CUST_DIM_1.INST_PROD_ID+0=CRM.CM_IP_SERVICE_DIM.INST_PROD_ID+0 and CRM.CM_CUST_DIM_1.AGG_LEVEL=1 )
AND (
CRM.CM_CUST_DIM_1.INST_PROD_ID = '67838442'
)