查看oracle锁表相关脚本

i45k247x4f

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

--根据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       14657 5330    SCOTT      TM Row-X(SX) None       9704 N 9ynqbf76bd2sa
easy13 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 None549 N ck2bbym0s5bqq
easy       14657 5330    SCOTT      AE Share None       9813 N 9ynqbf76bd2sa
easy       14657 5330    SCOTT      TO Row-X(SX) None       9723 N 9ynqbf76bd2sa
easy       13785 7563    SYS      AE Share None       2986 N brzzcb965abbn
easy2541 5881    SYS      AE Share None       7793 N 8f89p4938uzju 6qmwa2agzbhsf
easy       148     241 7594    HR      AE Share None       2922 N 23ghvtf35uumf
easy13 7 5327    SCOTT      AE Share None       9820 N 6ubd0c60r1tr9 56mu45ptpsyhq
easy13 7 5327    SCOTT      TX None Exclusive       9696 N 6ubd0c60r1tr9 56mu45ptpsyhq
easy       14657 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_USERB_ B_MODE   B_SQL_ID W_SID      W_USER W_ W_MODE       W_CTIME W_SQLID
-------------------- ---------- -- --------------- ------------- -------------------- ---------- -- --------------- ---------- -------------
1-146-57     SCOTTTX 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