oracle死锁的时候，如何得知死锁那一刻执行的dml、ddl操作是啥子啊？

[csdnhadoop]

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 -----

[csdnhadoop]

session 316 我看到sql是 一些了select语句在waiting吧。 session 709被锁住了，它执行的sql是啥啊？没有看到啊 我的想法是：我如果知道了session 709那一刻的sql，我就会回头让开发人员去查下为什么两者会死锁了。

[sych888]

V$SESSION 获取 SQL_ID ，然后一脚SQL_ID在V$SQLAREA获取SQL？

[csdnhadoop]

引用 4 楼 maitianhust 的回复:

好像有个记录历史的内部视图，找找看

名字是啥？

[csdnhadoop]

引用 2 楼 sych888 的回复:

V$SESSION 获取 SQL_ID ，然后一脚SQL_ID在V$SQLAREA获取SQL？

都过去好几个小时了啊，还能查询到当时的记录吗？

[maitianhust]

好像有个记录历史的内部视图，找找看

[maitianhust]

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；