oracle死锁的时候，怎么得知死锁那一刻执行的dml、ddl操作是啥子啊
oracle死锁的时候，如何得知死锁那一刻执行的dml、ddl操作是啥子啊？
Trace file /oracle/diag/rdbms/ipfsdb/ipfsdb/trace/ipfsdb_ora_27996.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /oracle/product/11.2.0/db_1
System name: Linux
Node name: tdb01
Release: 2.6.32.59-0.7-default
Version: #1 SMP 2012-07-13 15:50:56 +0200
Machine: x86_64
Instance name: ipfsdb
Redo thread mounted by this instance: 1
Oracle process number: 66
Unix process pid: 27996, image: oracle@tdb01
*** 2015-03-20 09:05:57.597
*** SESSION ID:(709.54395) 2015-03-20 09:05:57.597
*** CLIENT ID:() 2015-03-20 09:05:57.597
*** SERVICE NAME:(SYS$USERS) 2015-03-20 09:05:57.597
*** MODULE NAME:(JDBC Thin Client) 2015-03-20 09:05:57.597
*** ACTION NAME:() 2015-03-20 09:05:57.597
*** 2015-03-20 09:05:57.597
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-000b001d-000495e1 66 709 X 80 316 S
TX-0009001e-000251d2 80 316 X 66 709 S
session 709: DID 0001-0042-000C5160 session 316: DID 0001-0050-000BF4EE
session 316: DID 0001-0050-000BF4EE session 709: DID 0001-0042-000C5160
Rows waited on:
Session 709: obj - rowid = 00011284 - AAATcIAAGAAA+qNAAA
(dictionary objn - 70276, file - 6, block - 256653, slot - 0)
Session 316: no row
----- Information for the OTHER waiting sessions -----
Session 316:
sid: 316 ser: 47697 audsid: 14040982 user: 61/IPFS
flags: (0x45) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40008) -/-
pid: 80 O/S info: user: oracle, term: UNKNOWN, ospid: 28024
image: oracle@tdb01
client details:
O/S info: user: weblogic, term: unknown, ospid: 1234
machine: tapp01 program: JDBC Thin Client
application name: JDBC Thin Client, hash value=2546894660
current SQL:
SELECT onlineT.OT_ID,onlineT.VM_MERNO ,merchant.MER_NAME,merchant.RAMUS_ORGAN_ID,merchant.MER_ACCOUNT_NO,onlineT.CARD_TYPE,NVL(onlineT.Trade_Money,0),NVL(onlineT.FEE_T0,0),NVL(onlineT.FEE_T1,0),merchant.BLACK_FLAG , merchant.RISK_FLAG,merchant.GAME_TYPE ,CASE WHEN onlineT.CARD_TYPE IN ('02','03','04','06','08') THEN '1' ELSE '0' END DC_TYPE ,NVL(merchant.DEBIT_CARD_WARNNING_AMT,0) , NVL(merchant.CREDIT_CARD_WARNNING_AMT,0),NVL(merchant.DEBIT_CARD_MAX_AMT,0), NVL(merchant.CREDIT_CARD_MAX_AMT,0), org.ORG_LINK_MPHONE,org.SMS_NOTICE,org.ORG_LINK_MPHONE2,org.SMS_NOTICE2, NVL(merchant.DEBIT_CARD_MAX_AMT2,0), NVL(merchant.CREDIT_CARD_MAX_AMT2,0),merchant.STATE,merchant.RTN_FLAG,merchant.IFT0,merchant.bank_no, NVL(org.LIMIT_MONEY,0), NVL(org.LIMIT_MONEY2,0) FROM TBL_ONLINE_TRADE onlineT, T
----- End of information for the OTHER waiting sessions -----
------解决思路----------------------
V$SESSION 获取 SQL_ID ，然后一脚SQL_ID在V$SQLAREA获取SQL？
------解决思路----------------------
好像有个记录历史的内部视图，找找看
------解决思路----------------------
V$ACTIVE_SESSION_HISTORY
------解决思路----------------------
查询历史执行的sql
select b.SQL_TEXT,b.FIRST_LOAD_TIME,b.SQL_FULLTEXT
from v$sqlarea b
where b.FIRST_LOAD_TIME between '2012-12-12/09:24:47' and
'2012-12-12/10:24:47' order by b.FIRST_LOAD_TIME；
Trace file /oracle/diag/rdbms/ipfsdb/ipfsdb/trace/ipfsdb_ora_27996.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /oracle/product/11.2.0/db_1
System name: Linux
Node name: tdb01
Release: 2.6.32.59-0.7-default
Version: #1 SMP 2012-07-13 15:50:56 +0200
Machine: x86_64
Instance name: ipfsdb
Redo thread mounted by this instance: 1
Oracle process number: 66
Unix process pid: 27996, image: oracle@tdb01
*** 2015-03-20 09:05:57.597
*** SESSION ID:(709.54395) 2015-03-20 09:05:57.597
*** CLIENT ID:() 2015-03-20 09:05:57.597
*** SERVICE NAME:(SYS$USERS) 2015-03-20 09:05:57.597
*** MODULE NAME:(JDBC Thin Client) 2015-03-20 09:05:57.597
*** ACTION NAME:() 2015-03-20 09:05:57.597
*** 2015-03-20 09:05:57.597
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-000b001d-000495e1 66 709 X 80 316 S
TX-0009001e-000251d2 80 316 X 66 709 S
session 709: DID 0001-0042-000C5160 session 316: DID 0001-0050-000BF4EE
session 316: DID 0001-0050-000BF4EE session 709: DID 0001-0042-000C5160
Rows waited on:
Session 709: obj - rowid = 00011284 - AAATcIAAGAAA+qNAAA
(dictionary objn - 70276, file - 6, block - 256653, slot - 0)
Session 316: no row
----- Information for the OTHER waiting sessions -----
Session 316:
sid: 316 ser: 47697 audsid: 14040982 user: 61/IPFS
flags: (0x45) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40008) -/-
pid: 80 O/S info: user: oracle, term: UNKNOWN, ospid: 28024
image: oracle@tdb01
client details:
O/S info: user: weblogic, term: unknown, ospid: 1234
machine: tapp01 program: JDBC Thin Client
application name: JDBC Thin Client, hash value=2546894660
current SQL:
SELECT onlineT.OT_ID,onlineT.VM_MERNO ,merchant.MER_NAME,merchant.RAMUS_ORGAN_ID,merchant.MER_ACCOUNT_NO,onlineT.CARD_TYPE,NVL(onlineT.Trade_Money,0),NVL(onlineT.FEE_T0,0),NVL(onlineT.FEE_T1,0),merchant.BLACK_FLAG , merchant.RISK_FLAG,merchant.GAME_TYPE ,CASE WHEN onlineT.CARD_TYPE IN ('02','03','04','06','08') THEN '1' ELSE '0' END DC_TYPE ,NVL(merchant.DEBIT_CARD_WARNNING_AMT,0) , NVL(merchant.CREDIT_CARD_WARNNING_AMT,0),NVL(merchant.DEBIT_CARD_MAX_AMT,0), NVL(merchant.CREDIT_CARD_MAX_AMT,0), org.ORG_LINK_MPHONE,org.SMS_NOTICE,org.ORG_LINK_MPHONE2,org.SMS_NOTICE2, NVL(merchant.DEBIT_CARD_MAX_AMT2,0), NVL(merchant.CREDIT_CARD_MAX_AMT2,0),merchant.STATE,merchant.RTN_FLAG,merchant.IFT0,merchant.bank_no, NVL(org.LIMIT_MONEY,0), NVL(org.LIMIT_MONEY2,0) FROM TBL_ONLINE_TRADE onlineT, T
----- End of information for the OTHER waiting sessions -----
------解决思路----------------------
V$SESSION 获取 SQL_ID ，然后一脚SQL_ID在V$SQLAREA获取SQL？
------解决思路----------------------
好像有个记录历史的内部视图，找找看
------解决思路----------------------
V$ACTIVE_SESSION_HISTORY
------解决思路----------------------
查询历史执行的sql
select b.SQL_TEXT,b.FIRST_LOAD_TIME,b.SQL_FULLTEXT
from v$sqlarea b
where b.FIRST_LOAD_TIME between '2012-12-12/09:24:47' and
'2012-12-12/10:24:47' order by b.FIRST_LOAD_TIME；