列出对象锁（lock）信息及其被杀的会话（sid,serial#）信息

54yg

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"
select  nvl(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.ctime
from    V$LOCK L,  
    V$SESSION S,
    SYS.USER$ U1,
    SYS.OBJ$ T1
where   L.SID = S.SID  
and T1.OBJ# = decode(L.ID2,0,L.ID1,L.ID2)  
and U1.USER# = T1.OWNER#
and S.TYPE != 'BACKGROUND'
order by 1,2,5
/

会话1：

以scott用户登录创建一个表t1

[iyunv@Oel_10G ~]# su - oracle
[oracle@Oel_10G ~]$ sqlplus scott/oracle

SQL> 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获取不到锁暂时为挂起状态

[iyunv@Oel_10G ~]# su - oracle
[oracle@Oel_10G ~]$ sqlplus scott/oracle
SQL> update t1 set i=3 where i=1;


会话 3：

在会话3中更新t1表的一行， 由于t1表中有Row Exclusive锁，此时会话3获取不到锁暂时为挂起状态

[iyunv@Oel_10G ~]# su - oracle
[oracle@Oel_10G ~]$ sqlplus scott/oracle
SQL> update   t1 set i=5 where i=1;


会话 4：

新开一个会话，以sys登陆，执行lock_check.sql查看相关对象锁信息

[iyunv@Oel_10G ~]# su - oracle
[oracle@Oel_10G ~]$ sqlplus / as  sysdba

SQL>@check_lock.sql

Username   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                                    108
SCOTT      pts/2  544,8727      SCOTT.T1                            Row Exclusive                                     72
                                PUBLIC.V$SQLAREA_PLAN_HASH                               Exclusive                    72
SCOTT      pts/3  522,5542      SCOTT.T1                            Row Exclusive                                      6
                                PUBLIC.V$SQLAREA_PLAN_HASH                               Exclusive                     6

6 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.sql

SQL>@check_lock.sql

Username   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                                    388
SCOTT      pts/2  544,8727      SCOTT.T1                            Row Exclusive                                    352
                                PUBLIC.V$SQLAREA_PLAN_HASH                               Exclusive                   352


此时，会话3已被***