--step 1：查看被阻塞会话等待事件

select sid, event, username, lockwait, sql.sql_text
? from v$session s, v$sql sql
where s.sql_id = sql.sql_id
?? and sql.sql_text like '%&sql%';

-------------------------------------------------------------------------
??? 22??? enq: TX - row lock contention??? SCOTT??? 000000007F8C4DD8??? update emp set ename='dbking' where empno=7369
??? 23??? enq: TX - row lock contention??? SCOTT??? 000000007F8C4F98??? delete from emp where empno=7499

--step 2：查找阻塞的blocker

select sid, inst_id, blocking_instance, blocking_session
? from gv$session
where sid = 22;

-----------------------------------------------------------------------
??? 22??? 1??? 1??? 142

??? 或

select sid, inst_id, blocking_instance, blocking_session
? from gv$session
where sid in (22, 23);

----------------------------------

22??? 1??? 1??? 142
23??? 1??? 1??? 142

--step 3：清除blocker

确认要清除会话的sid和serial#：

select sid, serial#
? from gv$session
where inst_id = 1
?? and sid = 142;

--------------------------------------
??? 142??? 873

清除会话：

SQL> alter system kill session '142,873';

System altered.

------------------TOP-----------------------

select sql_text
? from v$sqltext a
where (a.hash_value, a.address) in
?????? (select decode(sql_hash_value, 0, prev_hash_value, sql_hash_value),
?????????????? decode(sql_hash_value, 0, prev_sql_addr, sql_address)
????????? from v$session b
???????? where b.paddr = (select addr from v$process c where c.pid = '&pid'))
order by piece asc;

cs