1. 查看是否存在死锁

  1. select username,lockwait,status,machine,program from v$session where sid in
  2.  (select session_id from v$locked_object)

如果有输出的结果，则说明有死锁，且能看到死锁的机器是哪一台。
字段说明：

  • Username：死锁语句所用的数据库用户；
  • Lockwait：死锁的状态，如果有内容表示被死锁。
  • Status： 状态，active表示被死锁
  • Machine： 死锁语句所在的机器。
  • Program： 产生死锁的语句主要来自哪个应用程序

    2. 查看被死锁SQL语句

    1. select sql_text from v$sql where hash_value in
    2. (select sql_hash_value from v$session where sid in
    3. (select session_id from v$locked_object))

    3. 死锁的解决方法

    3.1 查看导致死锁的进程

    1. SELECT s.username,l.OBJECT_ID,l.SESSION_ID,s.SERIAL#,
    2. l.ORACLE_USERNAME,l.OS_USER_NAME,l.PROCESS
    3. FROM V$LOCKED_OBJECT l,V$SESSION S WHERE l.SESSION_ID=S.SID;

    3.2 kill死锁的进程

    1. alter system kill session 'sid,serial#'; （其中sid=l.session_id）

    4. 查看死锁信息

    1. SELECT    bs.username "Blocking User", bs.username "DB User",
    2.         ws.username "Waiting User", bs.SID "SID", ws.SID "WSID",
    3.         bs.serial# "Serial#", bs.sql_address "address",
    4.         bs.sql_hash_value "Sql hash", bs.program "Blocking App",
    5.         ws.program "Waiting App", bs.machine "Blocking Machine",
    6.         ws.machine "Waiting Machine", bs.osuser "Blocking OS User",
    7.         ws.osuser "Waiting OS User", bs.serial# "Serial#",
    8.         ws.serial# "WSerial#",
    9.         DECODE (wk.TYPE,
    10.                 'MR', 'Media Recovery',
    11.                 'RT', 'Redo Thread',
    12.                 'UN', 'USER Name',
    13.                 'TX', 'Transaction',
    14.                 'TM', 'DML',
    15.                 'UL', 'PL/SQL USER LOCK',
    16.                 'DX', 'Distributed Xaction',
    17.                 'CF', 'Control FILE',
    18.                 'IS', 'Instance State',
    19.                 'FS', 'FILE SET',
    20.                 'IR', 'Instance Recovery',
    21.                 'ST', 'Disk SPACE Transaction',
    22.                 'TS', 'Temp Segment',
    23.                 'IV', 'Library Cache Invalidation',
    24.                 'LS', 'LOG START OR Switch',
    25.                 'RW', 'ROW Wait',
    26.                 'SQ', 'Sequence Number',
    27.                 'TE', 'Extend TABLE',
    28.                 'TT', 'Temp TABLE',
    29.                 wk.TYPE
    30.                ) lock_type,
    31.         DECODE (hk.lmode,
    32.                 0, 'None',
    33.                 1, 'NULL',
    34.                 2, 'ROW-S (SS)',
    35.                 3, 'ROW-X (SX)',
    36.                 4, 'SHARE',
    37.                 5, 'S/ROW-X (SSX)',
    38.                 6, 'EXCLUSIVE',
    39.                 TO_CHAR (hk.lmode)
    40.                ) mode_held,
    41.         DECODE (wk.request,
    42.                 0, 'None',
    43.                 1, 'NULL',
    44.                 2, 'ROW-S (SS)',
    45.                 3, 'ROW-X (SX)',
    46.                 4, 'SHARE',
    47.                 5, 'S/ROW-X (SSX)',
    48.                 6, 'EXCLUSIVE',
    49.                 TO_CHAR (wk.request)
    50.                ) mode_requested,
    51.         TO_CHAR (hk.id1) lock_id1, TO_CHAR (hk.id2) lock_id2,
    52.         DECODE
    53.            (hk.BLOCK,
    54.             0, 'NOT Blocking',          /**//* Not blocking any other processes */
    55.             1, 'Blocking',              /**//* This lock blocks other processes */
    56.             2, 'Global',           /**//* This lock is global, so we can't tell */
    57.             TO_CHAR (hk.BLOCK)
    58.            ) blocking_others
    59.    FROM v$lock hk, v$session bs, v$lock wk, v$session ws
    60.   WHERE hk.BLOCK = 1
    61.     AND hk.lmode != 0
    62.     AND hk.lmode != 1
    63.     AND wk.request != 0
    64.     AND wk.TYPE(+) = hk.TYPE
    65.     AND wk.id1(+) = hk.id1
    66.     AND wk.id2(+) = hk.id2
    67.     AND hk.SID = bs.SID(+)
    68.     AND wk.SID = ws.SID(+)
    69.     AND (bs.username IS NOT NULL)
    70.     AND (bs.username <> 'SYSTEM')
    71.     AND (bs.username <> 'SYS')
    72. ORDER BY 1;