Oracle检查死锁

检查死锁

检查死锁

用sysdba用户执行以下语句

select username,lockwait,status,machine,client_info,program 
    from gv$session 
    where sid in (select session_id from gv$locked_object);

## 如果有输出的结果，则说明有死锁，且能看到死锁的机器是哪一台。字段说明：
## 
## Username：死锁语句所用的数据库用户；
## 
## Lockwait：死锁的状态，如果有内容表示被死锁。
## 
## Status： 状态，active表示被死锁
## 
## Machine： 死锁语句所在的机器。
## 
## Program： 产生死锁的语句主要来自哪个应用程序

查看被死锁的语句

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;

kill死锁的进程

alter system kill session ‘sid,serial#’; (其中sid=l.session_id)
## 举例
sql > alter system kill session '71,1';

生成Kill Session语句

SELECT 'alter system kill session ''' || sid || ',' || serial# || ''';' "Deadlock"
  FROM v$session
 WHERE sid IN (SELECT sid FROM v$lock WHERE block = 1);

开启session记录IP地址

create or replace trigger login_on_record_ip
after logon on database
begin   
dbms_application_info.set_client_info(sys_context('userenv', 'ip_address'));
end;
/
select username,lockwait,status,machine,client_info,program 
from v$session 
where sid = 1155;

select trigger_name from all_triggers where trigger_name like '%RECORD%';

杀掉所有客户端名称包含doctor.exe的session

SELECT 'alter system kill session ''' || sid || ',' || serial# || ''';' "killcmd"
  FROM gv$session
 WHERE program like '%doctor.exe%';
set pagesize 1000 linesize 1000;
spool conninfo
select count(*), machine, program, username, osuser
  from gv$session
 group by machine, program, username, osuser
 order by count(*) desc;
 spool off
 exit