1：查询当前系统的等待事件
SQL> col event for a60
SQL>
SQL> select event,sid,p1,p2,p3 from v$session_wait where event not like 'SQL*%' and event not like 'rdbms%';
EVENT SID P1 P2 P3
------------------------------------------------------------ ---------- ---------- ---------- ----------
pmon timer 2 300 0 0
VKTM Logical Idle Wait 4 0 0 0
DIAG idle wait 6 5 1 1000
DIAG idle wait 8 5 1 1000
smon timer 13 300 0 0
Streams AQ: qmn coordinator idle wait 25 0 0 0
Space Manager: slave idle wait 28 1 0 0
Space Manager: slave idle wait 31 0 0 0
Streams AQ: waiting for time management or cleanup tasks 35 0 0 0
enq: TX - row lock contention 37 1415053318 327708 2318
Streams AQ: qmn slave idle wait 39 1 0 0
2：通过enq: TX - row lock contention来看看这些session都在等什么
SQL>
select SID,ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#
from v$session where event='enq: TX - row lock contention';
SID ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
---------- ------------- -------------- --------------- -------------
37 91916 5 1155 2
3：通过上面sql查找出来的对象编号查询对象属性
SQL> set pages 100 lines 180
SQL> col owner for a10
SQL> col object_name for a15
SQL> select OWNER,OBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID, OBJECT_TYPE from dba_objects where OBJECT_ID='91598';
OWNER OBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- --------------- ---------- -------------- --------------------------------------
TEST T2 91916 91916 TABLE
4：通过正在等待的SID查看它们都在执行什么操作
SQL> col sql_text for a100
SQL>
select a.sid,b.sql_text,b.sql_id from v$session a,v$sql b where sid
in(35) and (b.sql_id=a.sql_id or b.sql_id=a.prev_sql_id);
SID SQL_TEXT SQL_ID
----------
----------------------------------------------------------------------------------------------------
--------------------------
37 update t2 set id=1 where id=2 f9xtjya5z8hq6
5：下面我们去找一下对应sid产生的锁
SQL> select SID,TYPE,ID1,ID2,LMODE,REQUEST,CTIME,BLOCK from v$lock where block=1 or request0;
SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
36 TX 327708 2318 6 0 1345 1
37 TX 327708 2318 0 6 1292 0
LMODE为6表示持有锁的会话
6：根据sid查询pid信息
SQL> select SID,SERIAL# from v$session where sid=36;
SID SERIAL#
---------- ----------
36 7
7：kill阻塞会话
SQL> alter system kill session '36,7';
或者：
SQL> select 'alter system kill session '''||sid||','||serial#||''';' from v$session where sid=36;
'ALTERSYSTEMKILLSESSION'''||SID||','||SERIAL#||''';'
--------------------------------------------------------------------------------
alter system kill session '35,7';
8.如果在数据库会话层面杀会话的方法仍然无法释放死锁，就可以考虑到操作系统层面杀进程。
SQL>
select s.username,l.object_id, l.session_id,s.serial#,
s.lockwait,s.status,s.machine,s.program from v$session s,v$locked_object
l where s.sid = l.session_id;
USERNAME OBJECT_ID SESSION_ID SERIAL# LOCKWAIT STATUS MACHINE PROGRAM
----------
---------- ---------- ---------- ---------------- --------
---------------- ------------------------------------------------
SYS 87108 41 177 INACTIVE oracle11g sqlplus@oracle11g (TNS V1-V3)
SQL> select SID,SERIAL#,PADDR FROM V$SESSION WHERE SID='41';
SID SERIAL# PADDR
------- ---------- ----------------
41 177 00000000838C4D40
SQL> SELECT SPID FROM V$PROCESS WHERE ADDR='00000000838C4D40';
SPID
------------------------
12689
kill -9 12689
