表死锁查询

# 表死锁

```SQL
--RAC 锁表
SELECT S.SID,
         S.SERIAL#,
         P.SPID,
         S.STATUS,
         LO.LOCKED_MODE,
         LO.OBJECT_ID,
         OBJ.OBJECT_NAME,
         OBJ.OBJECT_TYPE,
         S.LAST_CALL_ET,
         S.PROCESS,
         LO.ORACLE_USERNAME,
         LO.OS_USER_NAME,
         S.INST_ID,
         S.*
    FROM GV$LOCKED_OBJECT LO, GV$SESSION S, DBA_OBJECTS OBJ, GV$PROCESS P
   WHERE LO.OBJECT_ID = OBJ.OBJECT_ID
     AND LO.SESSION_ID = S.SID 
         AND LO.INST_ID = S.INST_ID
     AND S.PADDR = P.ADDR 
         AND S.INST_ID = P.INST_ID
```

```SQL
--被锁对象查看
WITH LOCKED_OBJECT AS --查被锁的表
 (SELECT S.SID,
                 S.SERIAL#,
                 P.SPID,
                 S.STATUS,
                 LO.LOCKED_MODE,
                 LO.OBJECT_ID,
                 OBJ.OBJECT_NAME,
                 OBJ.OBJECT_TYPE,
                 S.LAST_CALL_ET,
                 S.PROCESS,
                 LO.ORACLE_USERNAME,
                 LO.OS_USER_NAME
        FROM V$LOCKED_OBJECT LO, V$SESSION S, DBA_OBJECTS OBJ, V$PROCESS P
     WHERE LO.OBJECT_ID = OBJ.OBJECT_ID
         AND LO.SESSION_ID = S.SID
         AND S.PADDR = P.ADDR),
SESSION_SQL AS --查session的sql
 (SELECT S.SID,
                 S.SERIAL#,
                 THIS_SQL.SQL_TEXT     THIS_SQL_TEXT,
                 THIS_SQL.SQL_FULLTEXT THIS_SQL_FULLTEXT,
                 PREV_SQL.SQL_TEXT     PREV_SQL_TEXT,
                 PREV_SQL.SQL_FULLTEXT PREV_SQL_FULLTEXT,
                 S.USERNAME,
                 S.SCHEMANAME
        FROM V$SESSION S, V$SQLAREA THIS_SQL, V$SQLAREA PREV_SQL
     WHERE S.SQL_HASH_VALUE = THIS_SQL.HASH_VALUE(+)
         AND S.PREV_HASH_VALUE = PREV_SQL.HASH_VALUE(+)),
SESSION_HISTORY_SQL AS --查session的历史sql
 (SELECT S.SESSION_ID SID,
                 S.SESSION_SERIAL# SERIAL#,
                 TO_CHAR(S.SAMPLE_TIME, 'yyyy-mm-dd hh24:mi:ss') SAMPLE_TIME,
                 SQL.SQL_TEXT,
                 SQL.SQL_FULLTEXT,
                 SQL.DISK_READS,
                 SQL.BUFFER_GETS,
                 SQL.CPU_TIME,
                 SQL.ROWS_PROCESSED
        FROM V$SQLAREA SQL, V$ACTIVE_SESSION_HISTORY S
     WHERE S.SQL_ID = SQL.SQL_ID)

SELECT *
    FROM LOCKED_OBJECT LO, SESSION_SQL SS, SESSION_HISTORY_SQL SHS
 WHERE LO.SID = SS.SID AND LO.SERIAL# = SS.SERIAL# AND LO.SID = SHS.SID AND LO.SERIAL# = SHS.SERIAL# AND ROWNUM <= 5;
```

```SQL
--查哪个session阻塞了哪个session
WITH LOCK_BLOCK AS
 (SELECT SID, TYPE, ID1, ID2, LMODE, REQUEST, CTIME, BLOCK, CON_ID
        FROM V$LOCK
     WHERE BLOCK > 0),
LOCK_REQUEST AS
 (SELECT SID, TYPE, ID1, ID2, LMODE, REQUEST, CTIME, BLOCK, CON_ID
        FROM V$LOCK
     WHERE REQUEST > 0),
SESSION_SQL AS --查session的sql
 (SELECT S.SID,
                 S.SERIAL#,
                 THIS_SQL.SQL_TEXT     THIS_SQL_TEXT,
                 THIS_SQL.SQL_FULLTEXT THIS_SQL_FULLTEXT,
                 PREV_SQL.SQL_TEXT     PREV_SQL_TEXT,
                 PREV_SQL.SQL_FULLTEXT PREV_SQL_FULLTEXT,
                 S.USERNAME,
                 S.SCHEMANAME,
                 S.MACHINE
        FROM V$SESSION S, V$SQLAREA THIS_SQL, V$SQLAREA PREV_SQL
     WHERE S.SQL_HASH_VALUE = THIS_SQL.HASH_VALUE(+)
         AND S.PREV_HASH_VALUE = PREV_SQL.HASH_VALUE(+))

SELECT LBS.USERNAME || '@' || LBS.MACHINE || ' ( SID=' || LBS.SID || ' ) is blocking ' || LRS.USERNAME || '@' || LRS.MACHINE || ' ( SID=' || LRS.SID || ' ) ' AS BLOCKING_STATUS,
             LBS.THIS_SQL_TEXT,
             LBS.PREV_SQL_TEXT,
       LRS.THIS_SQL_TEXT,
             LRS.PREV_SQL_TEXT,
       LB.*,
             LR.*
    FROM LOCK_BLOCK LB, LOCK_REQUEST LR, SESSION_SQL LBS, SESSION_SQL LRS
 WHERE LB.ID1 = LR.ID1
     AND LB.ID2 = LR.ID2
     AND LB.SID = LBS.SID
     AND LR.SID = LRS.SID;
```

```SQL
--查session历史sql记录
SELECT S.SESSION_ID SID,
                 S.SESSION_SERIAL# SERIAL#,
                 TO_CHAR(S.SAMPLE_TIME, 'yyyy-mm-dd hh24:mi:ss') SAMPLE_TIME,
                 SQL.SQL_TEXT,
                 SQL.SQL_FULLTEXT,
                 SQL.DISK_READS,
                 SQL.BUFFER_GETS,
                 SQL.CPU_TIME,
                 SQL.ROWS_PROCESSED
        FROM V$SQLAREA SQL, V$ACTIVE_SESSION_HISTORY S
     WHERE S.SQL_ID = SQL.SQL_ID
   AND S.SESSION_ID = 37
```

```SQL
--kill掉session  'sid,serial#,@inst'
alter system kill session 'sid,serial#,@inst' immediate;
alter system disconnect session '4,32509' [immediate];

SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' POST_TRANSACTION;

SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#,@inst' IMMEDIATE;
```

* 相关数据字典

| 序号  | 表名              | 描述            |
|:---:| --------------- | ------------- |
| 1   | v$session       | 记录会话及锁的信息     |
| 2   | v$session_wait  | 记录会话的等待信息     |
| 3   | v$lock          | 锁信息           |
| 4   | v$locked_object | 当前被锁对象的信息     |
| 5   | dba_locks       | 对V$lock的格式化视图 |
| 6   | dba_blockers    | 正在阻塞资源的会话     |
| 7   | dba_waiters     | 正在等待锁资源的会话    |
| 8   | v$latch         | 锁存器信息         |
| 9   | v$latch_misses  | latch争用的丢失统计  |