死锁

--1.知道被锁住的对象
--查找被锁的对象ID
--HAT表示用户
--HAT_LOCKS表示表
select object_id
from dba_objects
where owner='HAT' and object_name='HAT_LOCKS';

--查找该对象被谁锁定
--51174表示表HAT_LOCKS的OBJECT_ID
--ACTIVE表示死锁
--v$session表的STATUS有三种状态，分别是INACTIVE、ACTIVE和KILLED
select

object_name,s.sid,s.serial#,p.spid,s.username,l.os_user_name,l.process,s.lockwait,s.statu

s,s.machine,s.program,s.logon_time
from v$locked_object l , dba_objects o , v$session s , v$process p
where l.object_id=o.object_id and l.session_id=s.sid and s.paddr=p.addr and

l.object_id=51174 and s.status='ACTIVE'

--查找锁定该对象的语句
--51174表示表HAT_LOCKS的OBJECT_ID
--ACTIVE表示死锁
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 where object_id=51174) and status='ACTIVE');

--解锁
--alter system kill session 'sid,serial#';
alter system kill session '301,31';
--kill spid
kill 4755

--2.不知道被锁住的对象
--查找该对象被谁锁定
--ACTIVE表示死锁
--v$session表的STATUS有三种状态，分别是INACTIVE、ACTIVE和KILLED
select

object_name,s.sid,s.serial#,p.spid,s.username,l.os_user_name,l.process,s.lockwait,s.statu

s,s.machine,s.program,s.logon_time
from v$locked_object l , dba_objects o , v$session s , v$process p
where l.object_id=o.object_id and l.session_id=s.sid and s.paddr=p.addr and

s.status='ACTIVE'

--查找锁定该对象的语句
--ACTIVE表示死锁
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) and status='ACTIVE');

--解锁
--alter system kill session 'sid,serial#';
alter system kill session '301,31';
--kill spid
kill 4755