查看oracle锁表相关脚本

查看当前实例中锁的状态：

--根据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