1.查询被锁的对象:
select * from v$locked_object
字段:
object_id:数据表id
session_id:会话id
oracle_username:用户名
os_user_name:客户端计算机名
locked_mode:锁模式
2.查询被锁的表
select
a.session_id,
b.*
from v$locked_object a,dba_objects b
where b.object_id = a.object_id
字段:
session_id:会话id
owner:表的拥有者
object_name:表名
3.查询被锁对象的session
select
l.session_id,
l.object_id,
s.*
from v$locked_object l,v$session s
where l.session_id = s.sid
字段:
session_id:会话id
serial#:会话序列
object_id:数据表id
username:用户名
schemaname:用户名
osuser:操作客户端用户名
status:ACTIVE-已激活状态,INACTIVE-末激活状态,被阻塞状态
machine:客户端计算机名
program:客户程序
sql_hash_value:sql的哈希值
sql_id:sql的id
4.查询引起死锁的sql
select
l.session_id,
l.object_id,
q.*
from v$locked_object l,v$session s,v$sql q
where l.session_id = s.sid
and s.sql_id=q.sql_id
字段:
sql_text:sql语句
5.查询阻塞的会话id
select * from dba_waiters;
字段:
waiting_session:等待会话id
holding_session:阻塞会话id
6.杀掉死锁会话
alter system kill session 'session_id,serial#';
select * from v$locked_object
字段:
object_id:数据表id
session_id:会话id
oracle_username:用户名
os_user_name:客户端计算机名
locked_mode:锁模式
2.查询被锁的表
select
a.session_id,
b.*
from v$locked_object a,dba_objects b
where b.object_id = a.object_id
字段:
session_id:会话id
owner:表的拥有者
object_name:表名
3.查询被锁对象的session
select
l.session_id,
l.object_id,
s.*
from v$locked_object l,v$session s
where l.session_id = s.sid
字段:
session_id:会话id
serial#:会话序列
object_id:数据表id
username:用户名
schemaname:用户名
osuser:操作客户端用户名
status:ACTIVE-已激活状态,INACTIVE-末激活状态,被阻塞状态
machine:客户端计算机名
program:客户程序
sql_hash_value:sql的哈希值
sql_id:sql的id
4.查询引起死锁的sql
select
l.session_id,
l.object_id,
q.*
from v$locked_object l,v$session s,v$sql q
where l.session_id = s.sid
and s.sql_id=q.sql_id
字段:
sql_text:sql语句
5.查询阻塞的会话id
select * from dba_waiters;
字段:
waiting_session:等待会话id
holding_session:阻塞会话id
6.杀掉死锁会话
alter system kill session 'session_id,serial#';