Oracle锁处理、解锁方法
1、查询锁情况
selectsid,serial#,event,BLOCKING_SESSIONfromv$sessionwhereeventlike'%TX%';
2、根据SID查询具体信息(可忽略)
selectsid,serial#,username,machine,blocking_sessionfromv$sessionwheresid=;
3、杀掉会话
#根据1和2中查到的SID和SERIAL#定位会话，并杀掉
ALTERSYSTEMDISCONNECTSESSION', 'IMMEDIATE;
或
ALTERSYSTEMKILLSESSION', ';
附件：
#查询阻塞脚本
colwaiting_sessionfora20
collock_typefora15
colmode_requestedfora10
colmode_heldfora10
collock_id1fora10
collock_id2fora10
setlinesize120
setpagesize999
withdba_locks_custas
(SELECTinst_id||'_'||sidsession_id,
DECODE(TYPE,
'MR','MediaRecovery',
'RT','RedoThread',
'UN','UserName',
'TX','Transaction',
'TM','DML',
'UL','PL/SQLUserLock',
'DX','DistributedXaction',
'CF','ControlFile',
'IS','InstanceState',
'FS','FileSet',
'IR','InstanceRecovery',
'ST','DiskSpaceTransaction',
'TS','TempSegment',
'IV','LibraryCacheInvalidation',
'LS','LogStartorSwitch',
'RW','RowWait',
'SQ','SequenceNumber',
'TE','ExtendTable',
'TT','TempTable',
TYPE)
lock_type,
DECODE(lmode,
0,'None',/*MonLockequivalent*/
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,
0,'None',/*MonLockequivalent*/
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(request))
mode_requested,
TO_CHAR(id1)lock_id1,
TO_CHAR(id2)lock_id2,
ctimelast_convert,
DECODE(block,
0,'NotBlocking',/*Notblockinganyotherprocesses*/
1,'Blocking',/*Thislockblocksotherprocesses*/
2,'Global',/*Thislockisglobal,sowecan'ttell*/
TO_CHAR(block))
blocking_others
FROMgv$lock
),
lock_tempas
(select*fromdba_locks_cust),
lock_holderas
(
selectw.session_idwaiting_session,
h.session_idholding_session,
w.lock_type,
h.mode_held,
w.mode_requested,
w.lock_id1,
w.lock_id2
fromlock_tempw,lock_temph
whereh.blocking_othersin('Blocking','Global')
andh.mode_held!='None'
andh.mode_held!='Null'
andw.mode_requested!='None'
andw.lock_type=h.lock_type
andw.lock_id1=h.lock_id1
andw.lock_id2=h.lock_id2
),
lock_holdersas
(selectwaiting_session,holding_session,lock_type,mode_held,
mode_requested,lock_id1,lock_id2
fromlock_holder
unionall
selectholding_session,null,'None',null,null,null,null
fromlock_holder
minus
selectwaiting_session,null,'None',null,null,null,null
fromlock_holder
)
selectlpad('',3*(level-1))||waiting_sessionwaiting_session,
lock_type,
mode_requested,
mode_held,
lock_id1,
lock_id2
fromlock_holders
connectbypriorwaiting_session=holding_session
startwithholding_sessionisnull;
总结
以上所述是小编给大家介绍的Oracle锁处理、解锁方法，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对毛票票网站的支持！
声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：czq8825#qq.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。