oracle死锁经验

好多搜的百度的，总结一下

select l.session_id,o.owner,o.object_name

from v$locked_object l,dba_objects o
where l.object_id=o.object_id;
结果：
session_id owner object_name


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


select spid, osuser, s.program from v$session s,v$process p  where s.paddr=p.addr and s.sid=128;
结果：


alter system kill session '158,15184';
alter system kill session '146 ,8229';




 ssh 10.179.27.35
 pass:
 su - oracle
 sqlplus / as sysdba
 
 select spid, osuser, s.program from v$session s,v$process p  where s.paddr=p.addr and s.sid=203;
 
 SQL> exit
  ps -ef | grep oracle
  kill -9 13076
  
  
  
//查看正在运行的存储过程
select name,locks,pins
from v$db_object_cache
where locks > 0 and pins > 0 and type='PROCEDURE';


1.确定正在运行的过程名
select name,locks,pins
from v$db_object_cache
where locks > 0 and pins > 0 and type='PROCEDURE';
NAME                  LOCKS     PINS
P_ETL_CRM_DESK     1        1


2.查询v$open_cursor 视图
select sid,sql_text
from v$open_cursor
where UPPER(sql_text) like '%P_ETL_CRM_DESK%'
SID     SQL_TEXT
143     begin   -- Call the procedure   p_etl_crm_desk(v_dtdate => :


3.也可以用v$access确定
select * from v$access where object='P_ETL_CRM_DESK';
SID     OWNER     OBJECT                    TYPE
143     KDCC     P_ETL_CRM_DESK     PROCEDURE


4.或者dba_ddl_locks
select session_id sid, owner, name, type,mode_held held, mode_requested request
from dba_ddl_locks
where name = 'P_ETL_CRM_DESK';
SID     OWNER     NAME                             TYPE                   HELD     REQUEST
143     KDCC     P_ETL_CRM_DESK     Table/Procedure/Type     Null        None


查询正在执行的存储过程：
select *
from v$db_object_cache
where locks > 0 and pins > 0 and type='PROCEDURE';


在V$ACCESS视图中找到要停止进程的SID:
SELECT t.* FROM V$ACCESS t WHERE t.object='CRH_ENTRY';


在V$SESSION视图中查找到查出SID和SERIAL#
SELECT SID,SERIAL# FROM V$SESSION WHERE SID='1314';


杀掉查找出来的进程
alter system kill session '1314,63759';