1. 查看有无死锁对象 ,如有 kill session kill session
/* Formatted on 2010/8/18 9:51:59 (QP5 v5.115.810.9015) */
SELECT 'alter system kill session ''' || sid || ',' || serial# || ''';' "Deadlock"
FROM v$session
WHERE sid IN (SELECT sid
FROM v$lock
WHERE block = 1);
*******KIll session 语句
alter system kill session '391,48398'(sid 为 391);391);
注意: 应当注意对于 sid 在 100 以下的应当谨慎,可能该进程对某个 applicationapplication applicationapplicationapplication application ,如对应某个事务 ,可以 kill.
********查询导致死锁的SQL**
/* Formatted on 2010/8/18 0:06:11 (QP5 v5.115.810.9015) */
SELECT s.sid, q.sql_text
FROM v$sqltext q, v$session s
WHERE q.address = s.sql_address AND s.sid = &sid -- 这个&sid 是第一步查询出来的
ORDER BY piece
*******查看是谁锁了表**
/* Formatted on 2010/8/18 0:03:46 (QP5 v5.115.810.9015) */
SELECT /*+ rule */
LPAD (' ', DECODE (l.xidusn, 0, 3, 0))
|| l.oracle_username|| l.oracle_username|| l.oracle_username || l.oracle_username|| l.oracle_username|| l.oracle_username || l.oracle_username || l.oracle_username || l.oracle_username
User_name,
o.owner,
o.object_name,
o.object_type,
s.sid,
s.serial#
FROM v$locked_object l, dba_objects o, v$session s
WHERE l.object_id = o.object_id AND l.session_id = s.sid
ORDER BY o.object_id, xidusn DESC