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(发邮件时,请将#更换为@)进行举报,并提供相关证据,一经查实,本站将立刻删除涉嫌侵权内容。