oracle死锁_oracle批量杀进程_oracle等待事件

目 录
----------------------------------------------------------------------------------------------------
1、等待事件及锁的统计
2、查找相关等待事件的SQL
3、单独及批量查杀Oracle等待事件的Session。
4、如果批量查杀不生效，通过操作系统查杀进程，并重启数据库。
5、批量查杀死锁的几种方法：
6、如果Oracle里面杀不掉锁，则杀OS进程
7、查看被锁的表与对象
8、根据操作系统PID查找Oracle sql
9、查询阻塞会话的SID,用户名及DML操作语句(DS)
10、锁定的对象，类型，模式，机器名(DS)
11、阻塞者会话的sid、serial＃信息(DS)
12、查看当前被锁的session正在执行的sql语句(DS)
13、手工解锁，释放锁
----------------------------------------------------------------------------------------------------
1、等待事件及锁的统计
--等待事件统计
select event,count(*) from v$session_wait group by event order by 2 desc;

SQL> select event,count(*) from v$session_wait group by event order by 2 desc;
EVENT COUNT(*)
---------------------------------------------------------------- ----------
rdbms ipc message 12
DIAG idle wait 2
SQL*Net message from client 2
Space Manager: slave idle wait 2
Streams AQ: qmn coordinator idle wait 1
Streams AQ: waiting for time management or cleanup tasks 1
VKTM Logical Idle Wait 1
Streams AQ: qmn slave idle wait 1
smon timer 1
pmon timer 1
SQL*Net message to client 1
已选择11行。
--锁的信息查询(v$lock and block=1)
set lin 200
set pagesize 200
select l.SID,s.SERIAL#,l.TYPE,l.ID1,l.ID2,l.LMODE,l.REQUEST,l.CTIME,l.BLOCK from V$lock l,v$session s where l.block=1 or l.request<>0 and l.sid=s.sid;
--锁的信息批量杀(v$lock and block=1)
select 'ALTER SYSTEM KILL SESSION '''||l.SID||','||s.SERIAL#||''''||';' from V$lock l,v$session s where l.block=1 or l.request<>0 and l.sid=s.sid;

2、查找相关等待事件的SQL
--查到相关SESSION SID相关信息
set lin 200
set pagesize 200
select s.sid,s.SERIAL#,t.EVENT
from v$session_wait t,v$session s
where t.EVENT not in
('SQL*Net message from client', 'rdbms ipc message',
'SQL*Net more data from client', 'SQL*Net more data to client',
'SQL*Net message to client', 'jobq slave wait') and s.sid=t.sid;

SQL> set lin 200
SQL> set pagesize 200
SQL> select s.sid,s.SERIAL#,t.EVENT
2 from v$session_wait t,v$session s
3 where t.EVENT not in
4 ('SQL*Net message from client', 'rdbms ipc message',
5 'SQL*Net more data from client', 'SQL*Net more data to client',
6 'SQL*Net message to client', 'jobq slave wait') and s.sid=t.sid;
SID SERIAL# EVENT
---------- ---------- ----------------------------------------------------------------
1 1 pmon timer
2 1 VKTM Logical Idle Wait
3 1 DIAG idle wait
4 1 DIAG idle wait
9 3 Streams AQ: qmn coordinator idle wait
14 1 Streams AQ: qmn slave idle wait
17 15 Space Manager: slave idle wait
131 1 smon timer
140 1 Streams AQ: waiting for time management or cleanup tasks
143 13 Space Manager: slave idle wait
已选择10行。
--或者根据具体的等待事件查询
set lin 200
set pagesize 200
select s.sid,s.SERIAL#,t.EVENT
from v$session_wait t,v$session s
where t.EVENT='enq: TX - row lock contention' and s.sid=t.sid;

--根据SID查找相关SQL（单独）
set lin 200
set pagesize 200
select sql_text,HASH_VALUE from v$sqlarea where (hash_value,address)=(select sql_hash_value,sql_address from v$session where sid=&sid);
--根据SID查找相关SQL（多个）
select sid,sql_text from v$session a,v$sql b where sid in(4,12,41,212,232) and (b.sql_id=a.sql_id or b.sql_id=a.prev_sql_id);

--查看当前被锁的session正在执行的sql语句
select a.sql_text from v$sqlarea a,v$session b where a.sql_id=b.sql_id and b.event like'%TX%'
select /*+ NO_MERGE(a) NO_MERGE(b) NO_MERGE(c) */ a.username, a.machine, a.sid, a.serial#, a.last_call_et "Seconds", b.id1, c.sql_text "SQL" from v$session a, v$lock b, v$sqltext c where a.username is not null and a.lockwait = b.kaddr and c.hash_value =a.sql_hash_value

3、单独及批量查杀Oracle等待事件的Session。
--如果个别的，可以手工杀进程
alter system kill session '249,305';
--如果太多，可以批量杀进程（查看详细的，INACTIVE的）
set lin 200
set pagesize 200
select 'ALTER SYSTEM KILL SESSION '''||s.SID||','||s.SERIAL#||''''||';', 'kill -9 '||p.spid,
s.sid,s.serial#,s.username,s.status,s.terminal,s.osuser,s.machine,s.program,s.type,t.start_time
from v$session s,v$transaction t,v$process p
where t.addr=s.taddr
and s.PADDR=p.ADDR
order by t.start_time;
SQL> set lin 200
SQL> set pagesize 200
SQL> select 'ALTER SYSTEM KILL SESSION '''||s.SID||','||s.SERIAL#||''''||';', 'kill -9 '||p.spid,
2 s.sid,s.serial#,s.username,s.status,s.terminal,s.osuser,s.machine,s.program,s.type,t.start_time
3 from v$session s,v$transaction t,v$process p
4 where t.addr=s.taddr
5 and s.PADDR=p.ADDR
6 order by t.start_time;
'ALTERSYSTEMKILLSESSION'''||S.SID||','||S.SERIAL#||''''||';' 'KILL-9'||P.SPID
-------------------------------------------------------------------------------------------------------------- ----------------------
STATUS TERMINAL OSUSER MACHINE PROGRAM
-------- ---------------- ------------------------------ ---------------------------------------------------------------- -----------
START_TIME
--------------------
ALTER SYSTEM KILL SESSION '137,9'; kill -9 5128
INACTIVE FYKJ FYKJ\zhouling WORKGROUP\FYKJ sqlplus.exe
02/21/14 08:21:38
--如果太多，可以批量杀进程（批量杀进程并对应等待事件）
set lin 200
set pagesize 200
select 'ALTER SYSTEM KILL SESSION '''||t.SID||','||s.SERIAL#||''''||';'||t.EVENT
from v$session_wait t,v$session s
where t.EVENT not in
('SQL*Net message from client', 'rdbms ipc message',
'SQL*Net more data from client', 'SQL*Net more data to client',
'SQL*Net message to client', 'jobq slave wait') and t.sid=s.sid;
--如果太多，可以批量杀进程（批量杀进程）
set lin 200
set pagesize 200
select 'ALTER SYSTEM KILL SESSION '''||t.SID||','||s.SERIAL#||''''||';'
from v$session_wait t,v$session s
where t.EVENT not in
('SQL*Net message from client', 'rdbms ipc message',
'SQL*Net more data from client', 'SQL*Net more data to client',
'SQL*Net message to client', 'jobq slave wait') and t.sid=s.sid;
或
set lin 200
set pagesize 200
select 'ALTER SYSTEM KILL SESSION '''||t.SID||','||s.SERIAL#||''''||';'
from v$session_wait t,v$session s
where t.EVENT='enq: TX - row lock contention' and s.sid=t.sid;

SQL> set lin 200
SQL> set pagesize 200
SQL> select 'ALTER SYSTEM KILL SESSION '''||t.SID||','||s.SERIAL#||''''||';'
2 from v$session_wait t,v$session s
3 where t.EVENT not in
4 ('SQL*Net message from client', 'rdbms ipc message',
5 'SQL*Net more data from client', 'SQL*Net more data to client',
6 'SQL*Net message to client', 'jobq slave wait') and t.sid=s.sid;
'ALTERSYSTEMKILLSESSION'''||T.SID||','||S.SERIAL#||''''||';'
--------------------------------------------------------------------------------------------------------------
ALTER SYSTEM KILL SESSION '1,1';
ALTER SYSTEM KILL SESSION '2,1';
ALTER SYSTEM KILL SESSION '3,1';
ALTER SYSTEM KILL SESSION '4,1';
ALTER SYSTEM KILL SESSION '9,3';
ALTER SYSTEM KILL SESSION '14,1';
ALTER SYSTEM KILL SESSION '17,15';
ALTER SYSTEM KILL SESSION '131,1';
ALTER SYSTEM KILL SESSION '140,1';
ALTER SYSTEM KILL SESSION '143,13';
已选择10行。

4、如果批量查杀不生效，通过操作系统查杀进程，并重启数据库。
select 'ALTER SYSTEM KILL SESSION '''||s.SID||','||s.SERIAL#||''''||';', 'kill -9 '||p.spid,
s.sid,s.serial#,s.username,s.status,s.terminal,s.osuser,s.machine,s.program,s.type,t.start_time
from v$session s,v$transaction t,v$process p
where t.addr=s.taddr
and s.PADDR=p.ADDR
order by t.start_time;
ps -ef | grep LOCAL=NO | awk '{print $2}' | xargs kill -9
ps -ef|grep ora_j
ps -ef|grep ora_j | awk '{print $2}' | xargs kill -9
alter system checkpoint;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
shutdown immediate;

5、批量查杀死锁的几种方法：
--按用户名：
SELECT 'alter system kill session ''' || c.sid || '' || ',' || c.serial# ||''';', a.object_id, a.session_id, b.object_name, c.* FROM v$locked_object a, dba_objects b, v$session c WHERE a.object_id = b.object_id AND a.SESSION_ID = c.sid(+) AND schemaname = 'SYSTEM' ORDER BY logon_time;
--按主机名：
SELECT 'alter system kill session ''' || c.sid || '' || ',' || c.serial# ||''';', a.object_id, a.session_id, b.object_name, c.* FROM v$locked_object a, dba_objects b, v$session c WHERE a.object_id = b.object_id AND a.SESSION_ID = c.sid(+) AND MACHINE = 'FYKJ' ORDER BY logon_time;

6、如果Oracle里面杀不掉锁，则杀OS进程
方法一：
SQL> select xidusn, object_id, session_id, locked_mode from v$locked_object; --查死锁的对象，获取其SESSION_ID
XIDUSN OBJECT_ID SESSION_ID LOCKED_MODE
---------- ---------- ---------- -----------
10 30724 29 3
10 30649 29 3
SQL> select username,sid,serial# from v$session where sid=29; --根据上步获取到的sid查看其serial#号
USERNAME SID SERIAL#
------------------------------ ---------- ----------
QUIK 29 57107
SQL>alter system kill session '29,57107';
ORA-00031: session marked for kill
--删除进程，如已经删除过，则会报ora-00031的错误；否则oracle会将该session标记为killed状态，等待一段时间看能否会自动消失，如长时间消失不掉，则需要做后续步骤
SQL> select pro.spid from v$session ses,v$process pro where ses.sid=29 and ses.paddr=pro.addr; --查看spid号，以便在操作系统中根据该进程号删除进程
SPID
------------
2273286
# ps -ef|grep 2273286 --查看进程详情
root 2289864 2494636 0 17:07:15 pts/1 0:00 grep 2273286
oracle 2273286 1 0 14:38:24 - 0:21 oracleQUIK (LOCAL=NO)
# kill -9 2273286 --删除进程，小心操作，别写错进程号，如果oracle的关键进程被删，数据库会崩溃的！

方法二：
SELECT s.username,l.OBJECT_ID,l.SESSION_ID,s.SERIAL#,
l.ORACLE_USERNAME,l.OS_USER_NAME,l.PROCESS
FROM V$LOCKED_OBJECT l,V$SESSION S WHERE l.SESSION_ID=S.SID;
select pro.spid from v$session ses,
v$process pro where ses.sid="&sid" and
ses.paddr=pro.addr;

7、查看被锁的表与对象
--查看哪些对象及表被锁住了
set lin 2000
set pagesize 2000
COL OWNER format a10
COL OBJECT_NAME format a20
COL MACHINE format a20
COL ORACLE_USERNAME format a20
SELECT A.OWNER,
A.OBJECT_NAME,
B.XIDUSN,
B.XIDSLOT,
B.XIDSQN,
B.SESSION_ID,
B.ORACLE_USERNAME,
B.OS_USER_NAME,
B.PROCESS,
B.LOCKED_MODE,
C.MACHINE,
C.STATUS,
C.SERVER,
C.SID,
C.SERIAL#,
C.PROGRAM
FROM ALL_OBJECTS A,
V$LOCKED_OBJECT B,
SYS.GV_$SESSION C
WHERE ( A.OBJECT_ID = B.OBJECT_ID )
AND (B.PROCESS = C.PROCESS )
ORDER BY 1,2;
alter system kill session 'sid, serial#'
--查看锁表的时间与SQL
select b.username,b.sid,b.serial#,logon_time
from v$locked_object a,v$session b
where a.session_id = b.sid order by b.logon_time;
select t2.username||' '||t2.sid||'
'||t2.serial#||' '||t2.logon_time||'
'||t3.sql_text
from v$locked_object t1,v$session t2,v$sqltext t3
where t1.session_id=t2.sid
and t2.sql_address=t3.address
order by t2.logon_time;

--查看锁住表的session并杀掉
SELECT sn.username, m.SID,sn.SERIAL#, m.TYPE,
DECODE (m.lmode,
0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl.',
6, 'Exclusive',
lmode, LTRIM (TO_CHAR (lmode, '990'))
) lmode,
DECODE (m.request,
0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl.',
6, 'Exclusive',
request, LTRIM (TO_CHAR (m.request, '990'))
) request,
m.id1, m.id2
FROM v$session sn, v$lock m
WHERE (sn.SID = m.SID AND m.request != 0)
OR ( sn.SID = m.SID
AND m.request = 0
AND lmode != 4
AND (id1, id2) IN (
SELECT s.id1, s.id2
FROM v$lock s
WHERE request != 0 AND s.id1 = m.id1
AND s.id2 = m.id2)
)ORDER BY id1, id2, m.request;
alter system kill session 'sid, serial#'

8、根据操作系统PID查找Oracle sql
set lin 1000
set pagesize 1000
col USERNAME format a16
col MACHINE format a16
col SQL_TEXT format a500
SELECT a.username,a.machine,a.program,a.sid,a.serial#,a.status,c.piece,c.sql_text FROM v$session a,v$process b,v$sqltext c WHERE b.spid='&spid' AND b.addr=a.paddr AND a.sql_address=c.address(+) ORDER BY c.piece;
----------------------------------------------------------------------------------------------------------------------------------------

9、查询阻塞会话的SID,用户名及DML操作语句(DS)
由sys用户通过查询与锁相关的视图来了解锁，了解阻塞会话与被阻塞会话的sid、serial＃、用户名及其所使用的DML操作语句。
set pagesize 40
set linesize 150
column blockers format a45
column waiters format a45
select '阻塞者(' || sb.sid || ':' || sb.serial# || '-' || sb.username || ')-' ||
qb.sql_text blockers,
'等待者(' || sw.sid || ':' || sw.serial# || '-' || sw.username || ')-' ||
qw.sql_text waiters
from v$lock lb, v$lock lw, v$session sb, v$session sw, v$sql qb, v$sql qw
where lb.sid = sb.sid
and lw.sid = sw.sid
and sb.prev_sql_addr = qb.address
and sw.sql_address = qw.address
and lb.id1 = lw.id1
and sb.lockwait is null
and sw.lockwait is not null
and lb.block = 1;
BLOCKERS WAITERS
--------------------------------------------- ---------------------------------------------
阻塞者(154:39-SCOTT)-begin :id := sys.dbms_trans 等待者(146:142-SCOTT)- update emptest set deptno
action.local_transaction_id; end; =80 where empno=7369

10、锁定的对象，类型，模式，机器名(DS)
要了解哪些数据库用户的会话锁定了对象、锁定的模式是什么、对应的操作系统用户是在哪台计算机上进行操作的、被锁定的对象及其类型等信息
set pagesize 40
set linesize 150
column username format a9
column sid format 9999
column serial# format 99999999
column mode_locked format a12
column os_user_name format a16
column object_name format a12
column object_type format a12
select s.username,s.sid,s.serial#,decode(lo.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(lo.locked_mode)) mode_locked,lo.os_user_name,do.object_name,do.object_type from v$session s,v$locked_object lo,dba_objects do where lo.object_id=do.object_id;
USERNAME SID SERIAL# MODE_LOCKED OS_USER_NAME OBJECT_NAME OBJECT_TYPE
--------- ----- --------- ------------ ---------------- ------------ ------------
140 1 row-x(sx) FYKJ\zhouling WDZ1 TABLE
140 1 row-x(sx) FYKJ\zhouling T1_DEADLOCK TABLE
138 3 row-x(sx) FYKJ\zhouling WDZ1 TABLE
138 3 row-x(sx) FYKJ\zhouling T1_DEADLOCK TABLE
SYS 137 9 row-x(sx) FYKJ\zhouling WDZ1 TABLE
SYS 137 9 row-x(sx) FYKJ\zhouling T1_DEADLOCK TABLE
SYS 133 27 row-x(sx) FYKJ\zhouling WDZ1 TABLE
SYS 133 27 row-x(sx) FYKJ\zhouling T1_DEADLOCK TABLE
132 1 row-x(sx) FYKJ\zhouling WDZ1 TABLE
132 1 row-x(sx) FYKJ\zhouling T1_DEADLOCK TABLE

11、阻塞者会话的sid、serial＃信息(DS)
set pagesize 40
set linesize 150
column username format a9
column sid format 9999
column serial# format 99999999
select s.username,s.sid,s.serial# from v$session s,dba_blockers dbab where s.sid=dbab.holding_session;

12、查看当前被锁的session正在执行的sql语句(DS)
select /*+ NO_MERGE(a) NO_MERGE(b) NO_MERGE(c) */ a.username, a.machine, a.sid, a.serial#, a.last_call_et "Seconds", b.id1, c.sql_text "SQL" from v$session a, v$lock b, v$sqltext c where a.username is not null and a.lockwait = b.kaddr and c.hash_value =a.sql_hash_value;
USERNAME MACHINE SID SERIAL# Seconds ID1 SQL
--------- ------------------------------ ---- -------- ---------- ---------- ----------------------------------------------------------------
SCOTT WORKGROUP\CI-PC 146 142 423 65583 update emptest set deptno=80 where empno=7369

13、手工解锁，释放锁
SELECT A.SID,A.SERIAL#,A.USERNAME,B.TYPE FROM V$SESSION A,V$LOCK B WHERE A.SID=B.SID;
ALTER SYSTEM KILL SESSION 'SID,SERIAL#';
　　
手工释放锁
alter system kill session 'sid,serial#';
　　
注意： 应当注意对于sid在100以下的应当谨慎，可能该进程对应某个application，如对应某个事务,可以kill.