查看oracle死锁进程并结束死锁

查看oracle死锁进程并结束死锁博客分类：oracle数据库oracle死锁进程解锁查看锁表进程SQL语句1：selectsess.sid,sess.serial#,lo.oracle_username,lo.os_user_name,ao.object_name,lo.locked_modefromv$locked_objectlo,dba_objectsao,v$sessionsesswhereao.object_id=lo.object_idandlo.session_id=sess.sid;查看锁表进程SQL语句2：select*fromv$sessiont1,v$locked_objectt2wheret1.sid=t2.SESSION_ID;杀掉锁表进程：如有記錄則表示有lock，記錄下SID和serial#，將記錄的ID替換下面的738,1429，即可解除LOCKaltersystemkillsession'738,1429';用这个可以查：selects.sid,s.machine,o.object_name,l.oracle_username,l.locked_mode,'ALTERSYSTEMKILLSESSION'''||s.sid||','||s.serial#||''';'Commandfromv$locked_objectl,v$sessions,all_objectsowherel.session_id=s.sidandl.object_id=o.object_id可以查看哪台机器哪个用户锁了记录,其中command是用来杀掉锁住记录的session******************************************************************************************************************SELECTA.OBJECT_ID,B.OBJECT_NAME,A.SESSION_ID,A.ORACLE_USERNAME,A.OS_USER_NAME,A.PROCESS,A.LOCKED_MODEFROMV$LOCKED_OBJECTA,DBA_OBJECTSBWHEREA.OBJECT_ID=B.OBJECT_ID;SELECTT2.USERNAME,T2.SID,T2.SERIAL#,T2.LOGON_TIMEFROMV$LOCKED_OBJECTT1,V$SESSIONT2WHERET1.SESSION_ID=T2.SIDORDERBYT2.LOGON_TIME;ALTERSYSTEMKILLSESSION'sid,serial#';********************************************************************************************************************session1:C:\sqlplushxg/hxgSQLselect*fromscott.t;ABC--------------------------------------------------111aabb222helloworldSQLupdatescott.tsetb='good'wherea=222;已更新1行。session2:C:\sqlplusscott/tigerSQLselect*fromscott.t;ABC--------------------------------------------------111aabb222helloworldSQLupdatetsetb='asdfds'wherea=222;挂起。。。。session3:C:\sqlplussystem/***assysdbaSQLselectsid,serial#,username,statusfromv$session;SIDSERIAL#USERNAMESTATUS----------------------------------------------------14625SYSACTIVE14711SYSINACTIVE1482HXGINACTIVE1505SCOTTACTIVE1511ACTIVE1541ACTIVE1596ACTIVE1601ACTIVE1611ACTIVE1621ACTIVE1631ACTIVESIDSERIAL#USERNAMESTATUS----------------------------------------------------1641ACTIVE1651ACTIVE1661ACTIVE1671ACTIVE1681ACTIVE1691ACTIVE1701ACTIVE已选择18行。SQLaltersystemkillsession'148,2';系统已更改。SQLselectsid,serial#,username,statusfromv$session;SIDSERIAL#USERNAMESTATUS----------------------------------------------------14625SYSACTIVE14711SYSINACTIVE1482HXGKILLED1505SCOTTINACTIVE1511ACTIVE1541ACTIVE1596ACTIVE1601ACTIVE1611ACTIVE1621ACTIVE1631ACTIVESIDSERIAL#USERNAMESTATUS----------------------------------------------------1641ACTIVE1651ACTIVE1661ACTIVE1671ACTIVE1681ACTIVE1691ACTIVE1701ACTIVE已选择18行。SQLselectV$SESSION.sid,v$session.SERIAL#,v$process.spid,rtrim(object_type)object_type,rtrim(owner)||'.'||object_nameobject_name,decode(lmode,0,'None',1,'Null',2,'Row-S',3,'Row-X',4,'Share',5,'S/Row-X',6,'Exclusive','Unknown')LockMode,decode(request,0,'None',1,'Null',2,'Row-S',3,'Row-X',4,'Share',5,'S/Row-X',6,'Exclusive','Unknown')RequestMode,ctime,blockb,v$session.username,MACHINE,MODULE,ACTION,decode(A.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','LibraryCacheInvalida-tion','LS','LogStartorSwitch','RW','RowWait','SQ','SequenceNumber','TE','ExtendTable','TT','TempTable','Unknown')LockTypefrom(SELECT*FROMV$LOCK)A,all_objects,V$SESSION,v$processwhereA.sid6andobject_name'OBJ$'andA.id1=all_objects.object_idandA.sid=v$session.sidandv$process.addr=v$session.paddr;同样也是通过写sql从数据字典里查出来。//SELECT-SQL1//功能:检查被加锁的对象//selectobj.OWNER||'.'||obj.OBJECT_NAMEasOBJ_NAME,//对象名称(已经被锁住)//obj.SUBOBJECT_NAMEasSUBOBJ_NAME,//子对象名称(已经被锁住)//obj.OBJECT_IDasOBJ_ID,//对象ID//obj.OBJECT_TYPEasOBJ_TYPE,//对象类型//lock_obj.SESSION_IDasSESSION_ID,//会话SESSION_ID//lock_obj.ORACLE_USERNAMEasORA_USERNAME,//ORACLE系统用户名称//lock_obj.OS_USER_NAMEasOS_USERNAME,//操作系统用户名称//lock_obj.PROCESSasPROCESS//进程编号//from//(select*fromall_objectswhereobject_idin(selectobject_idfromv$locked_object))obj,//v$locked_objectlock_obj//whereobj.object_id=lock_obj.object_id;////SELECT-SQL2////功能:检查被加锁的对象以及加锁的会话信息////如果需要手工解除锁,请对照要解锁的对象,记下SESSION_ID,SERIAL#////项,然后运行下面的ALTER-SQL1//selectLOCK_INFO.OWNER||'.'||LOCK_INFO.OBJ_NAMEasOBJ_NAME,//对象名称(已经被锁住)//LOCK_INFO.SUBOBJ_NAMEasSUBOBJ_NAME,//子对象名称(已经被锁住)//SESS_INFO.MACHINEasMACHINE,//机器名称//LOCK_INFO.SESSION_IDasSESSION_ID,//会话SESSION_ID//SESS_INFO.SERIAL#asSERIAL#,//会话SERIAL#//LOCK_INFO.ORA_USERNAMEasORA_USERNAME,//ORACLE系统用户名称//LOCK_INFO.OS_USERNAMEasOS_USERNAME,//操作系统用户名称//LOCK_INFO.PROCESSasPROCESS,//进程编号//LOCK_INFO.OBJ_IDasOBJ_ID,//对象ID//LOCK_INFO.OBJ_TYPEasOBJ_TYPE,//对象类型//SESS_INFO.LOGON_TIMEasLOGON_TIME,//登录时间//SESS_INFO.PROGRAMasPROGRAM,//程序名称//SESS_INFO.STATUSasSTATUS,//会话状态//SESS_INFO.LOCKWAITasLOCKWAIT,//等待锁//SESS_INFO.ACTIONasACTION,//动作//SESS_INFO.CLIENT_INFOasCLIENT_INFO//客户信息////from//(//selectobj.OWNERasOWNER,//obj.OBJECT_NAMEasOBJ_NAME,//obj.SUBOBJECT_NAMEasSUBOBJ_NAME,//obj.OBJECT_IDasOBJ_ID,//obj.OBJECT_TYPEasOBJ_TYPE,//lock_obj.SESSION_IDasSES