死锁的问题

1. 检查数据库确定 是否 真实存在死锁，若有 哪台机器哪个程序。

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

–Username：死锁语句所用的数据库用户；
–Lockwait ：死锁的状态，如果有内容表示被死锁。
–Status ：状态，active表示被死锁
–Machine ：死锁语句所在的机器。
–Program ：产生死锁的语句主要来自哪个应用程序。

2. 确定死锁后，还可以检查是哪个语句产生死锁等待。

    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));

3. 查询未提交事务的SQL，大概率是其引起。

select s.sid,s.username, s.osuser,s.program,
       to_char(s.LOGON_TIME, 'yyyymmdd hh24:mi:ss')       as LOGON_TIME,
       to_char(t.START_DATE, 'yyyymmdd hh24:mi:ss')       as START_DATE,
       s.status,
       (select q.SQL_TEXT from v$sql q where q.LAST_ACTIVE_TIME = t.START_DATE and rownum <= 1) as SQL_TEXT
from v$session s, v$transaction t where s.sADDR = t.SES_ADDR;

5. 若找不到对应的User，则可以通过kill掉死锁的session进程

SELECT l.SESSION_ID, l.OS_USER_NAME, s.USERNAME, l.OBJECT_ID, l.ORACLE_USERNAME
FROM v$locked_object l,
     v$session s
WHERE l.SESSION_ID = s.SID;

6. 根据SessionID查询锁表语句

select sql_text from v$sql  where hash_value in
      (select sql_hash_value from v$session where sid in (208));

7. 查看死锁

select sess.sid, sess.serial#, lo.oracle_username, lo.os_user_name, ao.object_name, lo.locked_mode, SESS.machine
from v$locked_object lo,
     dba_objects ao,
     v$session sess
where ao.object_id = lo.object_id
  and lo.session_id = sess.sid;

8.对v$locked_object被锁对象进行查询：

```sql
 SELECT l.session_id sid,
             s.serial#,
             l.locked_mode,
             l.oracle_username,
             l.os_user_name,
             s.machine,
             s.terminal,
             o.object_name,
             s.logon_time
        FROM v$locked_object l, all_objects o, v$session s
       WHERE l.object_id = o.object_id AND l.session_id = s.sid
       ORDER BY sid, s.serial#;

9.杀死锁

```sql
select  SESSION_ID,ORACLE_USERNAME,PROCESS from V$LOCKED_OBJECT;
select  SID,SERIAL#,STATUS from V$SESSION where  SID in (select  SESSION_ID  from V$LOCKED_OBJECT);
alter system kill session '15,56440';  --上 15对应上面SID  ","  56440d对应SERIAL#