1、查询锁情况
sql;">
select sid,serial#,event,BLOCKING_SESSION from v$session where event like '%TX%';
2、根据SID查询具体信息(可忽略)
sql;">
select sid,username,machine,blocking_session from v$session where sid=;
3、杀掉会话
#根据1和2中查到的SID和SERIAL# 定位会话，并杀掉
sql;">
ALTER SYSTEM DISCONNECT SESSION ',' IMMEDIATE;
或
sql;">
ALTER SYSTEM KILL SESSION ',';
附件：
#查询阻塞脚本
sql;">
col waiting_session for a20
col lock_type for a15
col mode_requested for a10
col mode_held for a10
col lock_id1 for a10
col lock_id2 for a10
set linesize 120
set pagesize 999
with dba_locks_cust as
(SELECT inst_id||'_'||sid session_id,DECODE (TYPE,'MR','Media Recovery','RT','Redo Thread','UN','User Name','TX','Transaction','TM','DML','UL','PL/sql User Lock','DX','Distributed Xaction','CF','Control File','IS','Instance State','FS','File Set','IR','Instance Recovery','ST','Disk Space Transaction','TS','Temp Segment','IV','Library Cache Invalidation','LS','Log Start or Switch','RW','Row Wait','SQ','Sequence Number','TE','Extend Table','TT','Temp Table',TYPE)
lock_type,DECODE (lmode,'None',/* Mon Lock equivalent */
1,'Null',/* N */
2,'Row-S (SS)',/* L */
3,'Row-X (SX)',/* R */
4,'Share',/* S */
5,'S/Row-X (SSX)',/* C */
6,'Exclusive',/* X */
TO_CHAR (lmode))
mode_held,DECODE (request,/* X */
TO_CHAR (request))
mode_requested,TO_CHAR (id1) lock_id1,TO_CHAR (id2) lock_id2,ctime last_convert,DECODE (block,'Not Blocking',/* Not blocking any other processes */
1,'Blocking',/* This lock blocks other processes */
2,'Global',/* This lock is global,so we can't tell */
TO_CHAR (block))
blocking_others
FROM gv$lock
),lock_temp as
(select * from dba_locks_cust),lock_holder as
(
select w.session_id waiting_session,h.session_id holding_session,w.lock_type,h.mode_held,w.mode_requested,w.lock_id1,w.lock_id2
from lock_temp w,lock_temp h
where h.blocking_others in ('Blocking','Global')
and h.mode_held != 'None'
and h.mode_held != 'Null'
and w.mode_requested != 'None'
and w.lock_type = h.lock_type
and w.lock_id1 = h.lock_id1
and w.lock_id2 = h.lock_id2
),lock_holders as
(select waiting_session,holding_session,lock_type,mode_held,mode_requested,lock_id1,lock_id2
from lock_holder
union all
select holding_session,null,null
from lock_holder
minus
select waiting_session,null
from lock_holder
)
select lpad(' ',3*(level-1)) || waiting_session waiting_session,lock_id2
from lock_holders
connect by prior waiting_session = holding_session
start with holding_session is null;
总结
以上所述是小编给大家介绍的Oracle锁处理、解锁方法，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对编程之家网站的支持！
原文链接：https://www.f2er.com/oracle/64687.html