ORACLE TX鎖

T1YSL發表於2020-12-14
Oracle

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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/69990629/viewspace-2742203/，如需轉載，請註明出處，否則將追究法律責任。

相關文章