查询阻塞的常用语句
--谁阻塞了某个session(10g) （已验证）
SELECT sid,
username,
event,
blocking_session,
seconds_in_wait,
wait_time
FROM v$session
WHERE state IN ('WAITING') AND wait_class <> 'Idle';
--语句2：查询谁阻塞了谁 （已验证）
---查询谁阻塞了谁 blocked_sql_id
/* Formatted on 2014/9/20 11:48:53 (QP5 v5.256.13226.35538) */
SELECT '节点 '
|| a_s.INST_ID
|| ' session '
|| a_s.sid
|| ','
|| a_s.SERIAL#
|| ' 阻塞了 节点 '
|| b_s.INST_ID
|| ' session '
|| b_s.SID
|| ','
|| b_s.SERIAL#
blockinfo,
a_s.INST_ID,
a_s.SID,
a_s.SCHEMANAME,
a_s.MODULE,
a_s.STATUS,
'后为被阻塞信息',
b_s.INST_ID blocked_inst_id,
b_s.SID blocked_sid,
b_s.SCHEMANAME blocked_SCHEMANAME,
b_s.EVENT blocked_event,
b_s.MODULE blocked_module,
b_s.STATUS blocked_status,
b_s.SQL_ID SQL语句ID,
obj.owner 所有者,
obj.object_name 阻塞的类型名,
obj.OBJECT_TYPE 阻塞的类型,
CASE
WHEN b_s.ROW_WAIT_OBJ# <> -1
THEN
DBMS_ROWID.rowid_create (1,
obj.DATA_OBJECT_ID,
b_s.ROW_WAIT_FILE#,
b_s.ROW_WAIT_BLOCK#,
b_s.ROW_WAIT_ROW#)
ELSE
'-1'
END
blocked_rowid, --被阻塞数据的rowid
DECODE (obj.object_type,
'TABLE', 'select * from '
|| obj.owner
|| '.'
|| obj.object_name
|| ' where rowid='''
|| DBMS_ROWID.rowid_create (1,
obj.DATA_OBJECT_ID,
b_s.ROW_WAIT_FILE#,
b_s.ROW_WAIT_BLOCK#,
b_s.ROW_WAIT_ROW#)
|| '''',
NULL)
查询阻塞数据
FROM gv$session a_s, gv$session b_s, dba_objects obj
WHERE b_s.BLOCKING_INSTANCE IS NOT NULL
AND b_s.BLOCKING_SESSION IS NOT NULL
AND a_s.INST_ID = b_s.BLOCKING_INSTANCE
AND a_s.SID = b_s.BLOCKING_SESSION
AND b_s.ROW_WAIT_OBJ# = obj.object_id(+)
ORDER BY a_s.inst_id, a_s.sid
--查询谁阻塞了谁 带被阻塞的SQL语句
/* Formatted on 2014/9/20 11:50:30 (QP5 v5.256.13226.35538) */
SELECT /*+ ORDERED */
'阻塞者：('
|| A1.SID
|| ':'
|| A1.SCHEMANAME
|| ')-SQL:'
|| C1.SQL_TEXT
BLOCKERS,
A1.MACHINE,
A1.PROGRAM,
A1.LOGON_TIME,
'等待者：('
|| A.SID
|| ':'
|| A.SCHEMANAME
|| ')-SQL:'
|| C.SQL_TEXT
WAITERS,
A.MACHINE AS W_MACHINE,
A.PROGRAM AS W_PROGRAM,
A.LOGON_TIME AS W_LOGON_TIME
FROM V$LOCK B,
V$SESSION A,
V$SQLAREA C,
V$LOCK B1,
V$SESSION A1,
V$SQLAREA C1
WHERE A.LOCKWAIT = B.KADDR
AND C.HASH_VALUE(+) = A.SQL_HASH_VALUE
AND B1.REQUEST = 0
AND A1.USERNAME IS NOT NULL
AND A1.SID = B1.SID
AND C1.HASH_VALUE(+) = A1.SQL_HASH_VALUE
AND B1.ID1 = B.ID1
--查询生成阻塞树(发生TM锁等待)
/* Formatted on 2014/9/20 11:50:55 (QP5 v5.256.13226.35538) */
SELECT /*+ RULE */
LPAD ('--', DECODE (B.BLOCK, 1, 0, 4)) || S.USERNAME USER_NAME,
B.TYPE,
O.OWNER || '.' || O.OBJECT_NAME OBJECT_NAME,
S.SID,
S.SERIAL#,
DECODE (B.REQUEST, 0, 'BLOCKED', 'WATITING') STATUS,
S.EVENT
FROM DBA_OBJECTS O,
V$SESSION S,
V$LOCK V,
V$LOCK B
WHERE V.ID1 = O.OBJECT_ID
AND V.SID = S.SID
AND V.SID = B.SID
AND (B.BLOCK = 1 OR B.REQUEST > 0)
AND V.TYPE = 'TM'
ORDER BY B.ID2, V.ID1, USER_NAME DESC;
--kill掉这个死锁的进程：
alter system kill session 'sid,serial#'; （其中sid=l.session_id）
--锁表
/* Formatted on 2014/9/20 11:51:26 (QP5 v5.256.13226.35538) */
SELECT /*+ RULE */
LPAD (' ', DECODE (L.XIDUSN, 0, 3, 0)) || L.ORACLE_USERNAME
USER_NAME,
O.OWNER,
O.OBJECT_NAME,
O.OBJECT_TYPE,
--DECODE(L.TYPE, 'TM ', 'TABLE LOCK ', 'TX ', 'ROW LOCK ', NULL) LOCK_LEVEL,
DECODE (
L.LOCKED_MODE,
0, '[0] NONE ',
1, '[1] NULL 空 ',
2, '[2] ROW-S 行共享(RS)：共享表锁，SUB SHARE ',
3, '[3] ROW-X 行独占(RX) ：用于行的修改，SUB EXCLUSIVE ',
4, '[4] SHARE 共享锁(S)：阻止其他DML操作，SHARE ',
5, '[5] S/ROW-X 共享行独占(SRX)：阻止其他事务操作，SHARE/SUB EXCLUSIVE ',
6, '[6] EXCLUSIVE 独占(X)：独立访问使用，EXCLUSIVE ',
'[ ' || L.LOCKED_MODE || '] OTHER LOCK ')
LOCKMODE,
S.SID,
S.SERIAL#,
S.SQL_ADDRESS,
S.PROGRAM,
S.MACHINE,
S.SQL_HASH_VALUE
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;
--可以选择杀掉这个进程
alter system kill session '142,211';
------------------------------------------------------------------------------------------------------
---1）查找死锁的进程：
SELECT s.username,
l.object_id,
l.session_id,
s.serial#,
l.oracle_username,
l.os_user_name,
l.process,
'alter system kill session ''' || l.session_id || ',' || s.serial# || '''' as killSQL
FROM v$locked_object l, v$session s
WHERE l.session_id = s.SID
--2）kill掉这个死锁的进程：
alter system kill session 'sid,serial#'; （其中sid=l.session_id）
--3）如果还不能解决：
select pro.spid
from v$session ses, v$process pro
where ses.sid = 525
and ses.paddr = pro.addr;
--- 直接kill 这个进程解决
ps -ef| grep spid
[oracle@tan ~]$ ps -ef| grep 3813
oracle 3813 3811 0 19:16 ? 00:00:00 oracleorcl (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 4049 3910 0 19:48 pts/5 00:00:00 grep 3813
[oracle@tan ~]$ kill -9 3813 3811
/* 生成kill语句) */
SELECT a_s.owner,
a_s.object_name,
a_s.object_type,
VN.SID,
VN.SERIAL#,
DECODE (V_T.LOCKED_MODE,
0, 'none',
1, 'null', --可以某些情况下，如分布式数据库的查询会产生此锁
2, 'row-s(ss)', --表结构共享锁
3, 'row-x(sx)', --表结构共享锁+被操作的记录的排它锁
4, 'share', --表结构共享锁+所有记录共享锁
5, 's/row-x(ssx)', --表结构共享锁+所有记录排它锁
6, 'exclusive', --表结构排它锁+所有记录排它锁
TO_CHAR (V_T.LOCKED_MODE))
MODE_LOCKED,
VS.SPID "OS_PID",
VN.PROCESS "CLIENT_PID",
VN.USERNAME,
VN.OSUSER,
VN.MACHINE "HOSTNAME",
VN.TERMINAL,
VN.PROGRAM,
TO_CHAR (VN.LOGON_TIME, 'YYYY-MM-DD HH24:MI:SS') "LOGIN_TIME",
'alter system kill session ''' || vn.sid || ',' || vn.serial# || ''';'
"ORACLE_KILL",
'kill -9 ' || VS.SPID "OS_KILL"
FROM ALL_OBJECTS A_S,
V$LOCKED_OBJECT V_T,
V$SESSION VN,
V$PROCESS VS
WHERE A_S.OBJECT_ID = V_T.OBJECT_ID
AND V_T.SESSION_ID = VN.SID
AND VS.ADDR = VN.PADDR;
