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