oracle查找tx锁源头,ORACLE TX锁

查看sid

select sid from v$mystat where rownum=1；

查看tx锁源头 持有者

SELECT /*+rule */ DECODE(request,0,'Holder: ','Waiter: ') ||

sid sess, id1, id2, lmode, request, type,ctime

FROM V$LOCK

WHERE (id1, id2, type) IN (SELECT /*+rule */ id1, id2, type FROM V$LOCK WHERE request > 0)

ORDER BY ctime;

request>0为请求者 request=0为持有者

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

运行下面脚本可以抓到哪个SID,哪个SQL_ID,跑的SQL_TEXT

锁住了哪个SID,哪个SQL_ID,哪个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;