create table testlk
(
col1 integer
);
新增測試資料
insert into testlb select level from dual connect by level <= 500;
Session 1)以user:Orion登入刪資料
SQL>connect orion/orion
SQL>delete from testlk where col1 = 8;
已刪除1個資料列.
Session 2)以user:pisces登入
SQL>connect pisces/pisces
Session 2)刪同一筆資料結果被block住
SQL>delete from testlk where col1 = 8;
Session 3)以user:taurus登入
SQL>connect taurus/taurus
Session 3)刪另一筆資料
SQL>delete from testlk where col1 = 9;
已刪除1個資料列.
SQL>commit;
Session 4)以sysdba登入檢查是否有session被block住
將以下程式存成.sql檔,ex:c:\uncommit.sql
(reference=> http://www.praetoriate.com/oracle_tips_find_locks.htm):
set pagesize 200
set linesize 200
column owner format a10
column object_name format a10
column username format a10
column object_type format a10
column osuser format a30
column machine format a30
spool c:\1.log
select c.owner,c.object_name,c.object_type,
b.sid,b.serial#,b.username,b.blocking_session,b.status,b.osuser,
b.machine
from v$locked_object a ,v$session b,
dba_objects c
where b.sid = a.session_id
and a.object_id = c.object_id
/
spool off
執行uncommit.sql
SQL>@c:\uncommit.sql
開啟 1.log內容如下
OWNER OBJECT_NAM OBJECT_TYP SID SERIAL# USERNAME BLOCKING_SESSION STATUS OSUSER MACHINE
----- ---------- ---------- ---------- ---------- ---------- ---------------- ---------------- ------------------------------ --------
ORION TESTLK TABLE 156 256 PISCES 143 ACTIVE IBM-63AB93AA2BF\orion WORKGROUP\IBM-63AB93AA2BF
ORION TESTLK TABLE 143 71 ORION INACTIVE IBM-63AB93AA2BF\orion WORKGROUP\IBM-63AB93AA2BF 另外，也可以使用DBA_BLOCKERS來查看誰block住別人
以pisces登入刪資料
SQL>delete from testlk where col1 = 95;
已刪除 1 個資料列.
再以taurus登入刪同一筆資料
SQL>delete from testlk where col1 = 95;
以sys登入查誰block住別人
SQL>select * from dba_blockers;
HOLDING_SESSION
---------------
150
SQL>select username,program from v$session where sid = 150;
USERNAME PROGRAM
----------- -----------------
PISCES sqlplus.exe
如DBA_BLOCKERS不存在，則需先執行%ORACLE_HOME%\rdbms\admin\Catblock.sql產生之
