各位DBA，看到这篇文章是不是很开心，解决了你一个大麻烦，赶紧把它部署到实时监控程序吧
(咳咳，转载，抄袭不注明文章出处的人可耻哈)
session 1: update emp_bak set ename='沙雕' where empno=7369;
session 2: update emp_bak set ename='大长腿' where empno=7369;
session 3: update emp_bak set ename='矮丑穷' where empno=7369;
运行下面脚本可以抓到哪个SID,哪个SQL_ID,跑的SQL_TEXT锁住了哪个SID,哪个SQL_ID,哪个SQL语句
需要注意的是：如果V$SQLAREA没有保存SQL，可能抓不到，其次，如果系统并发很高，你可能需要再修改下脚本
select sysdate,
source_sid,
source_sql_id,
source_sql_text,
blocking_sid,
blocking_sql_id,
blocking_sql_text
from (select b.sid source_sid,
d.sql_id source_sql_id,
d.sql_text source_sql_text,
a.sid blocking_sid,
a.sql_id blocking_sql_id,
e.sql_text blocking_sql_text,
(select object_name
from dba_objects
where object_id = a.row_wait_obj#) object_name
from v$session a,
v$session b,
v$transaction c,
v$sqlarea d,
v$sqlarea e
where a.event = 'enq: TX - row lock contention'
and a.blocking_session = b.sid
and b.taddr = c.addr
and to_date(c.start_time, 'mm/dd/yy hh24:mi:ss') =
d.last_active_time
and d.command_type in (2, 3, 6)
and b.user# = d.parsing_schema_id
and a.sql_id = e.sql_id)
where instr(upper(source_sql_text), object_name) > 0;脚本运行示例：
SQL> select sysdate,
2 source_sid,
3 source_sql_id,
4 source_sql_text,
5 blocking_sid,
6 blocking_sql_id,
7 blocking_sql_text
8 from (select b.sid source_sid,
9 d.sql_id source_sql_id,
10 d.sql_text source_sql_text,
11 a.sid blocking_sid,
12 a.sql_id blocking_sql_id,
13 e.sql_text blocking_sql_text,
14 (select object_name
15 from dba_objects
16 where object_id = a.row_wait_obj#) object_name
17 from v$session a,
18 v$session b,
19 v$transaction c,
20 v$sqlarea d,
21 v$sqlarea e
22 where a.event = 'enq: TX - row lock contention'
23 and a.blocking_session = b.sid
24 and b.taddr = c.addr
25 and to_date(c.start_time, 'mm/dd/yy hh24:mi:ss') =
26 d.last_active_time
27 and d.command_type in (2, 3, 6)
28 and b.user# = d.parsing_schema_id
29 and a.sql_id = e.sql_id)
30 where instr(upper(source_sql_text), object_name) > 0;
SYSDATE SOURCE_SID SOURCE_SQL_ID SOURCE_SQL_TEXT BLOCKING_SID BLOCKING_SQL_ID BLOCKING_SQL_TEXT
----------- ---------- ------------- ------------------------------------------------ ------------ --------------- ------------------------------------------------
2020/5/19 1 192 201c4xcdsjaj0 update emp_bak set ename='沙雕' where empno=7369 4 2hpm4yjuut7cg update emp_bak set ename='矮丑穷' where empno=7369
2020/5/19 1 192 201c4xcdsjaj0 update emp_bak set ename='沙雕' where empno=7369 221 36xb1pyv12k56 update emp_bak set ename='大长腿' where empno=7369
