1、查询锁表的sid 和 serial#
SELECT s.sid, s.serial#
FROM v$locked_object lo, dba_objects ao, v$session s
WHERE ao.object_id = lo.object_id
AND lo.session_id = s.sid;
2、查询锁死的表名,用户
select sess.sid,
sess.serial#,
lo.oracle_username,
lo.os_user_name,
ao.object_name,
lo.locked_mode
from v$locked_object lo,
dba_objects ao,
v$session sess
where ao.object_id = lo.object_id and lo.session_id = sess.sid;
3、--找到该对象导致的锁的Session会话,并编写语句
杀死导致死锁的会话,怎么找关于这个表的session却没有提到,这里做个说明:
--找到表对象(表名要大写)
select * from dba_objects where object_name='TMP_DA_GMS'
--找到该对象导致的锁的Session会话,并编写语句(id1是第一个语句查询出的OBJECT_ID值)
select 'alter system kill session '''||SID||','||SERIAL#||''';' from V$session where SID in ( select sid from v$enqueue_lock t where t.type='TO' and id1='99879' )
--执行生成后的SQL语句解除死锁
例:要先执行第一步和第二步,查出相应的表名、sid和serial#
1)select * from dba_objects where object_name='BL_CLINIC_CONSULT';
2) select 'alter system kill session ''' || 673 || ',' || 415 || ''';'
from BL_CLINIC_CONSULT
where id in (select id
from BL_CLINIC_CONSULT t
WHERE
t.id = '673')
3) alter system kill session '1982,442';