列出對(duì)象鎖（lock）信息及其被殺的會(huì)話（sid,serial#）信息

check_lock.sql腳本 

列出對(duì)象鎖（lock）信息及其被殺的會(huì)話（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 
/ 

會(huì)話1：

以scott用戶登錄創(chuàng)建一個(gè)表t1

[root@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.

在會(huì)話1中給t1加一個(gè)鎖

SQL> select * from  t1 for update;

         I
----------
         1



會(huì)話 2：

在會(huì)話2中更新t1表的一行， 由于t1表中有Row Exclusive鎖，此時(shí)會(huì)話2獲取不到鎖暫時(shí)為掛起狀態(tài)

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


會(huì)話 3：

在會(huì)話3中更新t1表的一行， 由于t1表中有Row Exclusive鎖，此時(shí)會(huì)話3獲取不到鎖暫時(shí)為掛起狀態(tài)

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


會(huì)話 4：

新開一個(gè)會(huì)話，以sys登陸，執(zhí)行l(wèi)ock_check.sql查看相關(guān)對(duì)象鎖信息

[root@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.

執(zhí)行腳本后，結(jié)果列出表t1相關(guān)對(duì)象及鎖信息，會(huì)話1持有t1的行獨(dú)占鎖，會(huì)話2與會(huì)話3都在請(qǐng)求Exclusive鎖


現(xiàn)在我們kill掉scott用戶下sid,serial#為（522,5542）的會(huì)話

SQL> alter system kill session '522,5542';

System altered



在會(huì)話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


在會(huì)話4中繼續(xù)執(zhí)行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


此時(shí)，會(huì)話3已被***