本篇文章为大家展示了Oracle中有哪些锁处理方式，内容简明扼要并且容易理解，绝对能使你眼前一亮，通过这篇文章的详细介绍希望你能有所收获。

1、查询锁情况

selectsid,serial#,event,BLOCKING_SESSIONfromv$sessionwhereeventlike'%TX%';

2、根据SID查询具体信息(可忽略)

selectsid,serial#,username,machine,blocking_sessionfromv$sessionwheresid=<SID>;

3、杀掉会话

#根据1和2中查到的SID和SERIAL# 定位会话，并杀掉

ALTERSYSTEMDISCONNECTSESSION'<SID>,<SERIAL>'IMMEDIATE;

或

ALTERSYSTEMKILLSESSION'<SID>,<SERIAL>';

附件：

#查询阻塞脚本

colwaiting_sessionfora20collock_typefora15colmode_requestedfora10colmode_heldfora10collock_id1fora10collock_id2fora10setlinesize120setpagesize999withdba_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_othersFROMgv$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_id2fromlock_tempw,lock_temphwhereh.blocking_othersin('Blocking','Global')andh.mode_held!='None'andh.mode_held!='Null'andw.mode_requested!='None'andw.lock_type=h.lock_typeandw.lock_id1=h.lock_id1andw.lock_id2=h.lock_id2),lock_holdersas(selectwaiting_session,holding_session,lock_type,mode_held,mode_requested,lock_id1,lock_id2fromlock_holderunionallselectholding_session,null,'None',null,null,null,nullfromlock_holderminusselectwaiting_session,null,'None',null,null,null,nullfromlock_holder)selectlpad('',3*(level-1))||waiting_sessionwaiting_session,lock_type,mode_requested,mode_held,lock_id1,lock_id2fromlock_holdersconnectbypriorwaiting_session=holding_sessionstartwithholding_sessionisnull;

上述内容就是Oracle中有哪些锁处理方式，你们学到知识或技能了吗？如果还想学到更多技能或者丰富自己的知识储备，欢迎关注亿速云行业资讯频道。