check_lock.sql脚本 列出对象锁（lock）信息及其被杀的会话（sid,serial#）信息set linesize 132 pagesize 66 break on Kill on username on terminal column Kill heading 'Kill String' format a13 column res heading 'Resource Type' format 999 column id1 format 9999990 column id2 format 9999990 column lmode heading 'Lock Held' format a20 column request heading 'Lock Requested' format a20 column serial# format 99999 column username format a10 heading "Username" column terminal heading Term format a6 column tab format a35 heading "Table Name" column owner format a9 column Address format a18 column ctime heading "Seconds"selectnvl(S.USERNAME,'Internal') username, nvl(S.TERMINAL,'None') terminal, L.SID||','||S.SERIAL# Kill, U1.NAME||'.'||substr(T1.NAME,1,20) tab, decode(L.LMODE,1,'No Lock', 2,'Row Share', 3,'Row Exclusive', 4,'Share', 5,'Share Row Exclusive', 6,'Exclusive',null) lmode, decode(L.REQUEST,1,'No Lock', 2,'Row Share', 3,'Row Exclusive', 4,'Share', 5,'Share Row Exclusive', 6,'Exclusive',null) request, l.ctimefromV$LOCK L, V$SESSION S, SYS.USER$ U1, SYS.OBJ$ T1 whereL.SID = S.SID andT1.OBJ# = decode(L.ID2,0,L.ID1,L.ID2) andU1.USER# = T1.OWNER# andS.TYPE != 'BACKGROUND' order by 1,2,5 / 会话1：以scott用户登录创建一个表t1[root@Oel_10G ~]# su - oracle[oracle@Oel_10G ~]$ sqlplus scott/oracleSQL> create table t1 (i int); Table created.SQL> insert into t1 values(1);1 row created.SQL> commit;Commit complete.在会话1中给t1加一个锁SQL> select * from t1 for update; I---------- 1会话 2：在会话2中更新t1表的一行， 由于t1表中有Row Exclusive锁，此时会话2获取不到锁暂时为挂起状态[root@Oel_10G ~]# su - oracle[oracle@Oel_10G ~]$ sqlplus scott/oracleSQL> update t1 set i=3 where i=1;会话 3：在会话3中更新t1表的一行， 由于t1表中有Row Exclusive锁，此时会话3获取不到锁暂时为挂起状态[root@Oel_10G ~]# su - oracle[oracle@Oel_10G ~]$ sqlplus scott/oracleSQL> update t1 set i=5 where i=1;会话 4：新开一个会话，以sys登陆，执行lock_check.sql查看相关对象锁信息[root@Oel_10G ~]# su - oracle[oracle@Oel_10G ~]$ sqlplus / as sysdbaSQL>@check_lock.sqlUsername Term Kill String Table Name Lock Held Lock Requested Seconds---------- ------ ------------- ----------------------------------- -------------------- -------------------- ----------SCOTT pts/1 525,891 PUBLIC.V$SQLAREA_PLAN_HASH Exclusive 108 SCOTT.T1 Row Exclusive 108SCOTT pts/2 544,8727 SCOTT.T1 Row Exclusive 72 PUBLIC.V$SQLAREA_PLAN_HASH Exclusive 72SCOTT pts/3 522,5542 SCOTT.T1 Row Exclusive 6 PUBLIC.V$SQLAREA_PLAN_HASH Exclusive 66 rows selected.执行脚本后，结果列出表t1相关对象及锁信息，会话1持有t1的行独占锁，会话2与会话3都在请求Exclusive锁现在我们kill掉scott用户下sid,serial#为（522,5542）的会话SQL> alter system kill session '522,5542';System altered在会话3中可以看到(sid,serial#)(522,5542)被杀掉SQL> update t1 set i=5 where i=1;update t1 set i=5 where i=1 *ERROR at line 1:ORA-00028: your session has been killed在会话4中继续执行check_lock.sqlSQL>@check_lock.sqlUsername Term Kill String Table Name Lock Held Lock Requested Seconds---------- ------ ------------- ----------------------------------- -------------------- -------------------- ----------SCOTT pts/1 525,891 PUBLIC.V$SQLAREA_PLAN_HASH Exclusive 388 SCOTT.T1 Row Exclusive 388SCOTT pts/2 544,8727 SCOTT.T1 Row Exclusive 352 PUBLIC.V$SQLAREA_PLAN_HASH Exclusive 352此时，会话3已被删除
