實際開發和測試中，經常遇到資料物件有意無意鎖住的情況。怎麼樣最快速準確的定位到鎖住物件表的使用者和機器呢？

下面是筆者寫的一段指令碼，可以發現指定物件（特定Schema和特定物件）上所有加鎖物件和使用者。在Oracle中，只有獨佔鎖是唯一的，共享鎖可能存在多個使用者同時鎖住。筆者曾經遇到過使用pl/sql developer新增資料，程式反覆崩潰，最後發現這個物件表上加三次鎖的情況。

下面是針對這種情況，筆者試著寫的一段指令碼。執行環境可以是sql*plus或者pl/sql developer command window均可。只要輸入鎖住物件所在的schema和名稱，就可以定位到鎖定使用者資訊。

select p.SPID, p.SERIAL# as proserial,p.PID as pid, s.SID as sessionid,

s.SERIAL# as sessseri, s.USERNAME,s.OSUSER,s.MACHINE,

s.PROGRAM,s.ACTION,s.STATUS

from v$session s, v$process p

where s.PADDR=p.ADDR and s.SID in (

select session_id from v$locked_object

where object_id in (select object_id from dba_objects

where wner='&schema' and object_name='&name'));

其中的結果列中：

列名	含義	備註
SPID	作業系統級別的程式編號	在Linux/Unix環境下，使用ps –ef可以檢視到程式編號；而在windows環境下，是指定Oracle.exe程式中的執行緒編號！要注意區別！
PROSERIAL	作業系統級別的程式系列號	程式有一個系列號，在查詢和kill的時候要使用；
PID	在Oracle內部程式編號	Oracle內部的編號，可以查詢v$process檢視進行定位；
SESSIONID	鎖表會話ID	會話id；
SESSSERI	鎖表會話系列號	會話系列號，使用alter system kill session；
USERNAME	會話登入使用者名稱	登入使用者的資料庫登入名；
OSUSER	登入作業系統使用者名稱	登入使用者在作業系統上的名稱；
MACHINE	登入客戶端機器	客戶端主機名；
PROGRAM	登入程式名稱	登入程式名，如plsqldev.exe或者sqlplus.exe；
ACTION	進行行為描述	進行行為的簡單描述；
STATUS	當前會話狀態	當前會話狀態，可以為Active、InActive等；

使用該指令碼比較簡單，只要把裡面的SQL貼上在sqlplus或者其他開發工具上就可以了。下面以sqlplusw為例，進行演示。

SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 2月 16 14:08:51 2011

SQL> conn sys/acca@otstest as sysdba;

已連線。

SQL> select * from scott.emp for update nowait; //嘗試獲取獨佔！

select * from scott.emp for update nowait

*

第 1 行出現錯誤:

ORA-00054: 資源正忙, 但指定以 NOWAIT 方式獲取資源 //報錯，emp物件被鎖住了！！

呼叫指令碼。

SQL> select p.SPID, p.SERIAL# as proserial,p.PID as pid, s.SID as sessionid,

2 s.SERIAL# as sessseri, s.USERNAME,s.OSUSER,s.MACHINE,

3 s.PROGRAM,s.ACTION,s.STATUS

4 from v$session s, v$process p

5 where s.PADDR=p.ADDR and s.SID in (

6 select session_id from v$locked_object

7 where object_id in (select object_id from dba_objects

8 where wner='&schema' and object_name='&name'));

輸入 schema 的值: SCOTT

輸入 name 的值: EMP

原值 8: where wner='&schema' and object_name='&name'))

新值 8: where wner='SCOTT' and object_name='EMP'))

SPID PROSERIAL PID SESSIONID SESSSERI USERNAME OSUSER MACHINE PROGRAM ACTION STATUS

------------ ---------- ---------- ---------- ---------- ------------------------------ ------------------------------ ---------------------------------------------------------------- ---------------------------------------------------------------- -------------------------------- --------

3584 205 15 159 9529 SCOTT ACCA-119HS63YXC\Guest WORKGROUP\XXX-PC plsqldev.exe Command Window - New INACTIVE

這樣，就可以找到使用者資訊了。

下面解決就容易了，可以聯絡相關人員解開物件鎖，或者聯絡管理員kill掉會話和程式。這部分討論可以參見筆者之前的blog：

kill會話過程分析（http://space.itpub.net/17203031/viewspace-683786 ）。

最後，有興趣可以來分析一下指令碼使用的原資料檢視。

ü v$process：記錄了Oracle程式的資訊，包括Server Process和後臺程式，其中的SPID，Serial，以及ADDR是描述程式在作業系統層面的資訊；

ü v$session：記錄會話資訊，表示一個登入對應的會話內容；

ü v$lock_objects：當前被鎖住物件的資訊；

ü dba_objects：所有資料庫物件的列表，提供唯一object_id的來源；

ü

有一點要注意，並不只是資料表會被鎖住，儲存過程程式碼、檢視等物件都有被鎖住和引起連帶鎖住的風險。在本指令碼中資料這些物件的名稱，也是有一定作用的。

原則上說，當我們強制斷開資料庫Oracle客戶端之後，例項會自動檢測到連線的斷開，並且回收資源、撤銷事務和解鎖物件。這種操作一般是要有一定時間（幾秒鐘或者十幾分鍾），如果長時間沒有釋放資源，也就需要DBA的干預進行手工物件解鎖。

手工解鎖物件有兩個層面，會話層面和OS層面。一般我們採用從低到高的選擇方案。當會話層面的kill不能完全釋放資源的時候，才會考慮使用OS層面。一些特殊情況，如JOB程式，是需要直接OS層面殺死程式的。

誰鎖住了我的資料表

相關文章