檢視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;

ORACLE TX鎖

相關文章