oracle问题解决:ora-02049时分布式事务处理等待锁

oracle问题解决之ora-02049：超时：分布式事务处理等待锁

• 原因：使用DBLINK后，很容易出现一个问题:
  ORA-02049：超时：分布式事务处理等待锁
  ORA-02063：紧接着line（起自ODS_LINK）。
  事物没有提交或回滚导致锁。
• 基本思路：找到lock的sid和SERIAL，杀掉
• 命令： ALTER SYSTEM KILL SESSION ‘SID,SERIAL#’;
• 查找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;
select addr,sid,username,s.status,process,s.LAST_CALL_ET,s.program,START_DATE from
v$transaction t,v$session s
where t.ses_addr=s.saddr
order by LAST_CALL_ET;

select b.username,b.sid,b.serial#,logon_time
from v$locked_object a,v$session b
where a.session_id = b.sid order by b.logon_time;

select b.owner,b.object_name,a.session_id,a.locked_mode
from v$locked_object a,dba_objects b
where b.object_id = a.object_id;

select a.object_id,a.session_id,a.locked_mode,b.* from v$locked_object a,all_objects b
where a.OBJECT_ID=b.DATA_OBJECT_ID;

这篇好文章是转载于：学新通技术网