鸟不拉屎

ORA-02049:oracle超时分布式事务处理等待锁
查询死锁信息SELECT username, lockwait, status, ...
扫描右侧二维码阅读全文
16
2019/04

ORA-02049:oracle超时分布式事务处理等待锁

查询死锁信息

SELECT username,
       lockwait,
       status,
       machine,
       program
  FROM v$session
 WHERE sid IN (SELECT session_id FROM v$locked_object);

查找被锁的SQL语句

SELECT sql_text
  FROM v$sql
 WHERE hash_value IN (SELECT sql_hash_value
                        FROM v$session
                       WHERE sid IN (SELECT session_id FROM v$locked_object));

查找被死锁的进程

SELECT s.username,
       l.OBJECT_ID,
       l.SESSION_ID,
       s.SERIAL#,
       l.ORACLE_USERNAME,
       l.OS_USER_NAME,
       l.PROCESS
  FROM V$LOCKED_OBJECT l, V$SESSION S
 WHERE l.SESSION_ID = S.SID;

--或者

SELECT 
 S.USERNAME,
 DECODE(L.TYPE, 'TM', 'TABLE LOCK', 'TX', 'ROW LOCK', NULL) LOCK_LEVEL,
 O.OWNER,
 O.OBJECT_NAME,
 O.OBJECT_TYPE,
 S.SID,
 S.SERIAL#,
 S.TERMINAL,
 S.MACHINE,
 S.PROGRAM,
 S.OSUSER
  FROM V$SESSION S, V$LOCK L, DBA_OBJECTS O
 WHERE L.SID = S.SID
   AND L.ID1 = O.OBJECT_ID(+)
   AND S.USERNAME IS NOT NULL;

根据实际情况决定是否kill

alter system kill session 'sid,serial#';
文章名: 《ORA-02049:oracle超时分布式事务处理等待锁》
文章链接:https://niaobulashi.com/archives/oracle-TimeOut.html
除特别注明外,文章均为鸟不拉屎原创,转载时请注明本文出处及文章链接
公众号:鸟不拉屎(一个正在努力Coding的未来架构师)
Last modification:April 17th, 2019 at 07:59 pm

Leave a Comment