欢迎使用优快云-markdown编辑器

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