【轉】查看oracle死結進程並結束死結

標籤：process   row   rtrim(   state   user   number   code   ext   tar   

--查看鎖表進程SQL語句1：select sess.sid, sess.serial#, lo.oracle_username, lo.os_user_name, ao.object_name, lo.locked_modefrom v$locked_object lo, dba_objects ao, v$session sesswhere ao.object_id = lo.object_id and lo.session_id = sess.sid; --查看鎖表進程SQL語句2：select * from v$session t1, v$locked_object t2 where t1.sid = t2.SESSION_ID; --殺掉鎖表進程：--如有記錄則表示有lock，記錄下SID和serial# ，將記錄的ID替換下面的738,1429，即可解除LOCKalter system kill session ‘738,1429‘;  --用這個可以查（可以查看哪台機器哪個使用者鎖了記錄, 其中command是用來殺掉鎖住記錄的session ）：select s.sid,s.machine,o.object_name,l.oracle_username,l.locked_mode, ‘ALTER SYSTEM KILL SESSION ‘‘‘||s.sid||‘, ‘||s.serial#||‘‘‘;‘ Commandfrom v$locked_object l,v$session s,all_objects o where l.session_id=s.sid and l.object_id=o.object_id; select V$SESSION.sid,v$session.SERIAL#,v$process.spid,rtrim(object_type) object_type,rtrim(owner) || ‘.‘ || object_name object_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, block b,v$session.username,MACHINE,MODULE,ACTION,decode(A.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 Invalida-tion‘,‘LS‘, ‘Log Start or Switch‘,‘RW‘, ‘Row Wait‘,‘SQ‘, ‘Sequence Number‘,‘TE‘, ‘Extend Table‘,‘TT‘, ‘Temp Table‘,‘Unknown‘) LockTypefrom (SELECT * FROM V$LOCK) A, all_objects, V$SESSION, v$processwhere A.sid > 6and object_name<>‘OBJ$‘and A.id1 = all_objects.object_idand A.sid=v$session.sidand v$process.addr=v$session.paddr; --同樣也是通過寫sql從資料字典裡查出來。-- SELECT-SQL1 功能:檢查被加鎖的對象select obj.OWNER||‘.‘||obj.OBJECT_NAME as OBJ_NAME, --// 對象名稱(已經被鎖住)obj.SUBOBJECT_NAME as SUBOBJ_NAME, --// 子物件名稱(已經被鎖住)obj.OBJECT_ID as OBJ_ID, --// 對象IDobj.OBJECT_TYPE as OBJ_TYPE, --// 物件類型lock_obj.SESSION_ID as SESSION_ID, --// 會話SESSION_IDlock_obj.ORACLE_USERNAME as ORA_USERNAME, --// ORACLE系統使用者名稱稱lock_obj.OS_USER_NAME as OS_USERNAME, --// 作業系統使用者名稱稱lock_obj.PROCESS as PROCESS --// 進程編號from( select * from all_objects where object_id in (select object_id from v$locked_object)) obj,v$locked_object lock_objwhere obj.object_id=lock_obj.object_id; -- SELECT-SQL2 功能:檢查被加鎖的對象以及加鎖的會話資訊-- 如果需要手工解除鎖,請對照要解鎖的對象,記下SESSION_ID,SERIAL# 項,然後運行下面的ALTER-SQL1select LOCK_INFO.OWNER||‘.‘||LOCK_INFO.OBJ_NAME as OBJ_NAME, --// 對象名稱(已經被鎖住)LOCK_INFO.SUBOBJ_NAME as SUBOBJ_NAME, --// 子物件名稱(已經被鎖住)SESS_INFO.MACHINE as MACHINE, --// 機器名稱LOCK_INFO.SESSION_ID as SESSION_ID, --// 會話SESSION_IDSESS_INFO.SERIAL# as SERIAL#, --// 會話SERIAL#LOCK_INFO.ORA_USERNAME as ORA_USERNAME, --// ORACLE系統使用者名稱稱LOCK_INFO.OS_USERNAME as OS_USERNAME, --// 作業系統使用者名稱稱LOCK_INFO.PROCESS as PROCESS, --// 進程編號LOCK_INFO.OBJ_ID as OBJ_ID, --// 對象IDLOCK_INFO.OBJ_TYPE as OBJ_TYPE, --// 物件類型SESS_INFO.LOGON_TIME as LOGON_TIME, --// 登入時間SESS_INFO.PROGRAM as PROGRAM, --// 程式名稱SESS_INFO.STATUS as STATUS, --// 工作階段狀態SESS_INFO.LOCKWAIT as LOCKWAIT, --// 等待鎖SESS_INFO.ACTION as ACTION, --// 動作SESS_INFO.CLIENT_INFO as CLIENT_INFO --// 客戶資訊 from(select obj.OWNER as OWNER,obj.OBJECT_NAME as OBJ_NAME,obj.SUBOBJECT_NAME as SUBOBJ_NAME,obj.OBJECT_ID as OBJ_ID,obj.OBJECT_TYPE as OBJ_TYPE,lock_obj.SESSION_ID as SESSION_ID,lock_obj.ORACLE_USERNAME as ORA_USERNAME,lock_obj.OS_USER_NAME as OS_USERNAME,lock_obj.PROCESS as PROCESSfrom( select * from all_objects where object_id in (select object_id from v$locked_object)) obj,v$locked_object lock_objwhere obj.object_id=lock_obj.object_id) LOCK_INFO,(select SID,SERIAL#,LOCKWAIT,STATUS,PROGRAM,ACTION,CLIENT_INFO,LOGON_TIME,MACHINEfrom v$session) SESS_INFOwhere LOCK_INFO.SESSION_ID=SESS_INFO.SID ;   -- 看清楚了 下面就可以 殺死它了。-- ALTER-SQL1 功能:殺死會話(SESSION_ID,SERIAL#),可以手工解除鎖-- 請手工修改SESSION_ID,SERIAL#為相應值-- 注意:本功能謹慎使用,有一定的破壞性,該SQL可以斷開客戶機和伺服器的串連ALTER SYSTEM KILL SESSION ‘SESSION_ID,SERIAL#‘; 

【轉】查看oracle死結進程並結束死結