查看oracle死鎖進程并結束死鎖

  1 查看鎖表進程SQL語句1： 
  2 select sess.sid, 
  3    sess.serial#, 
  4    lo.oracle_username, 
  5    lo.os_user_name, 
  6    ao.object_name, 
  7    lo.locked_mode 
  8    from v$locked_object lo, 
  9    dba_objects ao, 
 10    v$session sess 
 11 where ao.object_id = lo.object_id and lo.session_id = sess.sid; 
 12 
 13 查看鎖表進程SQL語句2： 
 14 select * from v$session t1, v$locked_object t2 where t1.sid = t2.SESSION_ID; 
 15 
 16 殺掉鎖表進程： 
 17 如有記錄則表示有lock，記錄下SID和serial# ，將記錄的ID替換下面的738,1429，即可解除LOCK 
 18 alter system kill session '738,1429'; 
 19 
 20 
 21 用這個可以查：  select s.sid,s.machine,o.object_name,l.oracle_username,l.locked_mode, 'ALTER  SYSTEM  KILL  SESSION  '''||s.sid||',  '||s.serial#||''';'   Command  from  v$locked_object  l,v$session  s,all_objects  o  where  l.session_id=s.sid  and  l.object_id=o.object_id  可以查看哪臺機器哪個用戶鎖了記錄, 其中command是用來殺掉鎖住記錄的session 
 22 ****************************************************************************************************************** 
 23 SELECT        A.OBJECT_ID,        B.OBJECT_NAME,        A.SESSION_ID,        A.ORACLE_USERNAME,        A.OS_USER_NAME,        A.PROCESS,        A.LOCKED_MODE FROM V$LOCKED_OBJECT A, DBA_OBJECTS B WHERE A.OBJECT_ID = B.OBJECT_ID; 
 24 SELECT T2.USERNAME, T2.SID, T2.SERIAL#, T2.LOGON_TIME FROM V$LOCKED_OBJECT T1, V$SESSION T2 WHERE T1.SESSION_ID = T2.SID ORDER BY T2.LOGON_TIME; 
 25 ALTER SYSTEM KILL SESSION 'sid, serial#'; 
 26 ******************************************************************************************************************** 
 27 session 1: C:\>sqlplus hxg/hxg 
 28 SQL> select * from scott.t; 
 29          A B                    C ---------- -------------------- --------------------        111 aa                   bb        222 hello                world 
 30 SQL> update scott.t  set b='good' where a=222; 
 31 已更新 1 行。 
 32 session 2: C:\>sqlplus scott/tiger 
 33 SQL> select * from scott.t; 
 34          A B                    C ---------- -------------------- --------------------        111 aa                   bb        222 hello                world 
 35 SQL> update t set b='asdfds' where a=222; 
 36 掛起。。。。 
 37 session 3: C:\>sqlplus "system/*** as sysdba" 
 38 SQL> select sid,serial#,username,status from v$session; 
 39  SID    SERIAL# USERNAME                       STATUS ---- ---------- ------------------------------ --------  146         25 SYS                            ACTIVE  147         11 SYS                            INACTIVE  148          2 HXG                            INACTIVE  150          5 SCOTT                          ACTIVE  151          1                                ACTIVE  154          1                                ACTIVE  159          6                                ACTIVE  160          1                                ACTIVE  161          1                                ACTIVE  162          1                                ACTIVE  163          1                                ACTIVE 
 40  SID    SERIAL# USERNAME                       STATUS ---- ---------- ------------------------------ --------  164          1                                ACTIVE  165          1                                ACTIVE  166          1                                ACTIVE  167          1                                ACTIVE  168          1                                ACTIVE  169          1                                ACTIVE  170          1                                ACTIVE 
 41 已選擇18行。 
 42 SQL> alter system kill session '148,2'; 
 43 系統已更改。 
 44 SQL> select sid,serial#,username,status from v$session; 
 45  SID    SERIAL# USERNAME                       STATUS ---- ---------- ------------------------------ --------  146         25 SYS                            ACTIVE  147         11 SYS                            INACTIVE  148          2 HXG                            KILLED  150          5 SCOTT                          INACTIVE  151          1                                ACTIVE  154          1                                ACTIVE  159          6                                ACTIVE  160          1                                ACTIVE  161          1                                ACTIVE  162          1                                ACTIVE  163          1                                ACTIVE 
 46  SID    SERIAL# USERNAME                       STATUS ---- ---------- ------------------------------ --------  164          1                                ACTIVE  165          1                                ACTIVE  166          1                                ACTIVE  167          1                                ACTIVE  168          1                                ACTIVE  169          1                                ACTIVE  170          1                                ACTIVE 
 47 已選擇18行。 
 48 SQL> 
 49 
 50 
 51 select   V$SESSION.sid,v$session.SERIAL#,v$process.spid,   
 52   rtrim(object_type)   object_type,rtrim(owner)   ||   '.'   ||   object_name   object_name,   
 53   decode(lmode,       0,   'None',   
 54   1,   'Null',   
 55   2,   'Row-S',   
 56   3,   'Row-X',   
 57   4,   'Share',   
 58   5,   'S/Row-X',   
 59   6,   'Exclusive', 'Unknown')   LockMode,   
 60   decode(request,   0,   'None',   
 61   1,   'Null',   
 62   2,   'Row-S',   
 63   3,   'Row-X',   
 64   4,   'Share',   
 65   5,   'S/Row-X',   
 66   6,   'Exclusive',   'Unknown')   RequestMode   
 67   ,ctime,   block   b,   
 68   v$session.username,MACHINE,MODULE,ACTION,   
 69   decode(A.type,   
 70   'MR',   'Media   Recovery',   
 71   'RT','Redo   Thread',   
 72   'UN','User   Name',   
 73   'TX',   'Transaction',   
 74   'TM',   'DML',   
 75   'UL',   'PL/SQL   User   Lock',   
 76   'DX',   'Distributed   Xaction',   
 77   'CF',   'Control   File',   
 78   'IS',   'Instance   State',   
 79   'FS',   'File   Set',   
 80   'IR',   'Instance   Recovery',   
 81   'ST',   'Disk   Space   Transaction',   
 82   'TS',   'Temp   Segment',   
 83   'IV',   'Library   Cache   Invalida-tion',   
 84   'LS',   'Log   Start   or   Switch',   
 85   'RW',   'Row   Wait',   
 86   'SQ',   'Sequence   Number',   
 87   'TE',   'Extend   Table',   
 88   'TT',   'Temp   Table',   
 89   'Unknown')   LockType   
 90   from   (SELECT   *   FROM   V$LOCK)   A,   all_objects,V$SESSION,v$process   
 91   where   A.sid   >   6   
 92   and   object_name<>'OBJ$'   
 93   and   A.id1   =   all_objects.object_id   
 94   and   A.sid=v$session.sid   
 95   and   v$process.addr=v$session.paddr; 
 96 
 97 
 98 同樣也是通過寫sql從數據字典里查出來。 
 99 
100 // SELECT-SQL1 
101 // 功能:檢查被加鎖的對象 
102 //select obj.OWNER||'.'||obj.OBJECT_NAME as OBJ_NAME, // 對象名稱(已經被鎖住) 
103 //   obj.SUBOBJECT_NAME as SUBOBJ_NAME,     // 子對象名稱(已經被鎖住) 
104 //   obj.OBJECT_ID as OBJ_ID,        // 對象ID 
105 //   obj.OBJECT_TYPE as OBJ_TYPE,       // 對象類型 
106 //   lock_obj.SESSION_ID as SESSION_ID,     // 會話SESSION_ID 
107 //   lock_obj.ORACLE_USERNAME as ORA_USERNAME,  // ORACLE系統用戶名稱 
108 //   lock_obj.OS_USER_NAME as OS_USERNAME,    // 操作系統用戶名稱 
109 //   lock_obj.PROCESS as PROCESS       // 進程編號 
110 //from 
111 //  ( select * from all_objects where object_id in (select object_id from v$locked_object)) obj, 
112 //  v$locked_object lock_obj 
113 //where obj.object_id=lock_obj.object_id; 
114 
115 //// SELECT-SQL2 
116 //// 功能:檢查被加鎖的對象以及加鎖的會話信息 
117 ////  如果需要手工解除鎖,請對照要解鎖的對象,記下SESSION_ID,SERIAL# 
118 
119 //// 項,然后運行下面的ALTER-SQL1 
120 //select LOCK_INFO.OWNER||'.'||LOCK_INFO.OBJ_NAME as OBJ_NAME, // 對象名稱(已經被鎖住) 
121 //   LOCK_INFO.SUBOBJ_NAME as SUBOBJ_NAME,       // 子對象名稱(已經被鎖住) 
122 //   SESS_INFO.MACHINE as MACHINE,         // 機器名稱 
123 //   LOCK_INFO.SESSION_ID as SESSION_ID,       // 會話SESSION_ID 
124 //   SESS_INFO.SERIAL# as SERIAL#,         // 會話SERIAL# 
125 //   LOCK_INFO.ORA_USERNAME as ORA_USERNAME,      // ORACLE系統用戶名稱 
126 //   LOCK_INFO.OS_USERNAME as OS_USERNAME,       // 操作系統用戶名稱 
127 //   LOCK_INFO.PROCESS as PROCESS,         // 進程編號 
128 //   LOCK_INFO.OBJ_ID as OBJ_ID,          // 對象ID 
129 //   LOCK_INFO.OBJ_TYPE as OBJ_TYPE,         // 對象類型 
130 //   SESS_INFO.LOGON_TIME as LOGON_TIME,       // 登錄時間 
131 //   SESS_INFO.PROGRAM  as PROGRAM,         // 程序名稱 
132 //   SESS_INFO.STATUS as STATUS,          // 會話狀態 
133 //   SESS_INFO.LOCKWAIT as LOCKWAIT,         // 等待鎖 
134 //   SESS_INFO.ACTION as ACTION,          // 動作 
135 //   SESS_INFO.CLIENT_INFO as CLIENT_INFO       // 客戶信息 
136 // 
137 //from 
138 // ( 
139 //  select obj.OWNER as OWNER, 
140 //     obj.OBJECT_NAME as OBJ_NAME, 
141 //     obj.SUBOBJECT_NAME as SUBOBJ_NAME, 
142 //     obj.OBJECT_ID as OBJ_ID, 
143 //     obj.OBJECT_TYPE as OBJ_TYPE, 
144 //     lock_obj.SESSION_ID as SESSION_ID, 
145 //     lock_obj.ORACLE_USERNAME as ORA_USERNAME, 
146 //     lock_obj.OS_USER_NAME as OS_USERNAME, 
147 //     lock_obj.PROCESS as PROCESS 
148 //  from 
149 //   ( select * from all_objects where object_id in (select object_id from v$locked_object)) obj, 
150 //   v$locked_object lock_obj 
151 //  where obj.object_id=lock_obj.object_id 
152 // )  LOCK_INFO, 
153 // ( 
154 //  select SID, 
155 //     SERIAL#, 
156 //     LOCKWAIT, 
157 //     STATUS, 
158 //     PROGRAM, 
159 //     ACTION, 
160 //     CLIENT_INFO, 
161 //     LOGON_TIME, 
162 //     MACHINE 
163 //  from v$session 
164 // ) SESS_INFO 
165 //where LOCK_INFO.SESSION_ID=SESS_INFO.SID ; 
166 
167 
168 
169 //// 看清楚了 下面就可以 殺死它了。 
170 
171 // ALTER-SQL1 
172 // 功能:殺死會話(SESSION_ID,SERIAL#),可以手工解除鎖 
173 //    請手工修改SESSION_ID,SERIAL#為相應值 
174 // 注意:本功能慎重使用,有一定的破壞性,該SQL可以斷開客戶機和服務器的連接 
175 
176 //ALTER SYSTEM KILL SESSION 'SESSION_ID,SERIAL#'; 


轉自：https://www.iteye.com/blog/sqcjy111-1183928
posted @ 2021-07-05 09:38 管風琴在角落閱讀(268) 評論(0) 收藏舉報
刷新頁面返回頂部