查看当前实例中锁的状态:
--根据gv$lock查询锁的状况
col user_name for a10
col instance for a10
col pid for a7
col sid for 999999
col serial# for 999999
col hold_mode for a15
col req_mode for a15
set pagesize 20
select
i.instance_name instance,
s.sid sid,
s.serial# serial#,
p.spid pid,
s.username user_name,
l.type lock_type,
DECODE (l.LMODE,0, 'None',1, 'Null', 2, 'Row-S(SS)',3, 'Row-X(SX)', 4, 'Share',5, 'S/Row-X (SSX) ',6, 'Exclusive ',TO_CHAR (l.LMODE)) hold_mode,
DECODE (l.request,0, 'None',1, 'Null', 2, 'Row-S(SS)',3, 'Row-X(SX)', 4, 'Share',5, 'S/Row-X (SSX) ',6, 'Exclusive ',TO_CHAR (l.request)) req_mode,
l.ctime ctime,
decode(l.block,1,'Y','N') blocker,
s.prev_sql_id prev_sqlid,
s.sql_id sql_id
from
gv$instance i, gv$session s,gv$process p, gv$lock l
where
i.inst_id = s.inst_id
and s.inst_id = p.inst_id
and s.paddr = p.addr
and s.inst_id = l.inst_id
and s.sid = l.sid
order by blocker;
INSTANCE SID SERIAL# PID USER_NAME LO HOLD_MODE REQ_MODE CTIME B PREV_SQLID SQL_ID
---------- ------- ------- ------- ---------- -- --------------- --------------- ---------- - ------------- -------------
easy 146 57 5330 SCOTT TM Row-X(SX) None 9704 N 9ynqbf76bd2sa
easy 13 7 5327 SCOTT TM Row-X(SX) None 9696 N 6ubd0c60r1tr9 56mu45ptpsyhq
easy 125 5 4682 SYS AE Share None 11303 N ahc9t761kxw30 ahc9t761kxw30
easy 143 275 8319 SYS AE Share None 549 N ck2bbym0s5bqq
easy 146 57 5330 SCOTT AE Share None 9813 N 9ynqbf76bd2sa
easy 146 57 5330 SCOTT TO Row-X(SX) None 9723 N 9ynqbf76bd2sa
easy 137 85 7563 SYS AE Share None 2986 N brzzcb965abbn
easy 25 41 5881 SYS AE Share None 7793 N 8f89p4938uzju 6qmwa2agzbhsf
easy 148 241 7594 HR AE Share None 2922 N 23ghvtf35uumf
easy 13 7 5327 SCOTT AE Share None 9820 N 6ubd0c60r1tr9 56mu45ptpsyhq
easy 13 7 5327 SCOTT TX None Exclusive 9696 N 6ubd0c60r1tr9 56mu45ptpsyhq
easy 146 57 5330 SCOTT TX Exclusive None 9704 Y 9ynqbf76bd2sa
查看当前session的阻塞情况:
--根据gv$lock查看当前session的阻塞情况
col b_sid for a20
col w_sid for a20
col b_user for a10
col w_user for a10
col b_mode for a15
col w_mode for a15
set pagesize 20
select
bs.inst_id||'-'||bs.sid||'-'||bs.serial# b_sid,
bs.username b_user,
bl.type b_type,
DECODE (bl.LMODE,0, 'None',1, 'Null', 2, 'Row-S(SS)',3, 'Row-X(SX)', 4, 'Share',5, 'S/Row-X (SSX) ',6, 'Exclusive ',TO_CHAR (bl.LMODE)) b_mode,
bs.prev_sql_id b_sql_id,
ws.inst_id||'-'||ws.sid||'-'||ws.serial# w_sid,
ws.username w_user,
wl.type w_type,
DECODE (wl.request,0, 'None',1, 'Null', 2, 'Row-S(SS)',3, 'Row-X(SX)', 4, 'Share',5, 'S/Row-X (SSX) ',6, 'Exclusive ',TO_CHAR (wl.request)) w_mode,
wl.ctime w_ctime,
ws.sql_id w_sqlid
from
gv$lock bl,gv$lock wl,gv$session bs,gv$session ws
where bl.id1 =wl.id1
and bl.id2 = wl.id2
and bl.block=1
and bl.kaddr <> wl.kaddr
and bl.inst_id = bs.inst_id
and bl.sid = bs.sid
and wl.inst_id = ws.inst_id
and wl.sid = ws.sid;
B_SID B_USER B_ B_MODE B_SQL_ID W_SID W_USER W_ W_MODE W_CTIME W_SQLID
-------------------- ---------- -- --------------- ------------- -------------------- ---------- -- --------------- ---------- -------------
1-146-57 SCOTT TX Exclusive 9ynqbf76bd2sa 1-13-7 SCOTT TX Exclusive 9771 56mu45ptpsyhq
kill当前的阻塞session
--根据v$lock查询当前阻塞其他用户的进程,并生存删除脚本kill_block_session 和kill_block_process
set pagesize 0
set echo off
set feedback off
spool kill_block_session.sql
select
'alter system kill session '''||s.sid||','||serial#||''';'
from
v$session s, v$lock l
where
s.sid = l.sid
and s.username not in('SYS','SYSTEM')
and s.username is not null
and l.block = 1;
spool off
spool kill_block_process.sql
select
'kill -9 '||p.spid
from
v$session s, v$lock l,v$process p
where
s.sid = l.sid
and s.username not in('SYS','SYSTEM')
and s.username is not null
and s.paddr = p.addr
and l.block = 1;
spool off