Oracle 查看 對象 持有 鎖 的情況

 

同事在測試庫上對一個表加欄位，提示 ORA-00054， 資源忙。 應該是表對象的鎖沒有釋放。

有關Oracle 鎖的說明，參考：

ORACLE鎖機制

http://blog.csdn.net/tianlesoftware/article/details/4696896

 

用如下SQL 查看一下系統中相關對象上鎖的情況：

 

/* Formatted on 2012/2/13 14:24:32 (QP5 v5.185.11230.41888) */SELECT S.SID SESSION_ID,       S.USERNAME,       DECODE (LMODE,               0, ' None ',               1, ' Null ',               2, ' Row-S(SS) ',               3, ' Row-X(SX) ',               4, ' Share',               5, 'S/Row-X (SSX) ',               6, 'Exclusive ',               TO_CHAR (LMODE))          MODE_HELD,       DECODE (REQUEST,               0, ' None ',               1, ' Null ',               2, ' Row-S(SS) ',               3, ' Row-X(SX) ',               4, ' Share',               5, 'S/Row-X (SSX) ',               6, 'Exclusive ',               TO_CHAR (REQUEST))          MODE_REQUESTED,       O.OWNER || ' . ' || O.OBJECT_NAME || ' ( ' || O.OBJECT_TYPE || ' ) '          AS OBJECT_NAME,       S.TYPE LOCK_TYPE,       L.ID1 LOCK_ID1,       L.ID2 LOCK_ID2  FROM V$LOCK L, SYS.DBA_OBJECTS O, V$SESSION S WHERE L.SID = S.SID AND L.ID1 = O.OBJECT_ID AND object_name = 'xxxx';
 
 
該SQL 顯示所有對象上的鎖，如果要查某個具體的對象，可以根據OBJECT_NAME 欄位進行一下過濾，找到對應的SID 之後去查V$SESSION 視圖。 
 
該視圖會顯示session 對應的資訊，包括終端的資訊，如果找到了終端，可以讓它提交或者復原一下就OK了。 我這裡是測試環境，直接把session kill 掉了。然後修改表就ok了。
 
SQL>alter system kill session 'SID,SERIAL#'
後來測試了發現，上面的指令碼有時候會查不到對象的的相關記錄，故增加了V$ACCESS視圖，通過對這個視圖進行對象的判斷，修改之後的指令碼如下：
SELECT S.SID SESSION_ID,       S.USERNAME,       DECODE (LMODE,               0, ' None ',               1, ' Null ',               2, ' Row-S(SS) ',               3, ' Row-X(SX) ',               4, ' Share',               5, 'S/Row-X (SSX) ',               6, 'Exclusive ',               TO_CHAR (LMODE))          MODE_HELD,       DECODE (REQUEST,               0, ' None ',               1, ' Null ',               2, ' Row-S(SS) ',               3, ' Row-X(SX) ',               4, ' Share',               5, 'S/Row-X (SSX) ',               6, 'Exclusive ',               TO_CHAR (REQUEST))          MODE_REQUESTED,       O.OWNER || ' . ' || O.OBJECT_NAME || ' ( ' || O.OBJECT_TYPE || ' ) '          AS OBJECT_NAME,       S.TYPE LOCK_TYPE,       L.ID1 LOCK_ID1,       L.ID2 LOCK_ID2  FROM V$LOCK L, SYS.DBA_OBJECTS O, V$SESSION S,V$ACCESS A WHERE L.SID = S.SID AND L.ID1 = O.OBJECT_ID AND S.SID=A.SID AND A.OBJECT= 'PROC_VALIDATE_RULE_V3';
再次修改了一下，添加了V$SQL視圖，這樣可以一起查出具體導致這種鎖的SQL語句，一次性就搞定了，SQL如下：
/* Formatted on 2012/6/6 10:59:49 (QP5 v5.185.11230.41888) */SELECT distinct S.SID SESSION_ID,       S.STATUS,       S.USERNAME,       DECODE (LMODE,               0, ' None ',               1, ' Null ',               2, ' Row-S(SS) ',               3, ' Row-X(SX) ',               4, ' Share',               5, 'S/Row-X (SSX) ',               6, 'Exclusive ',               TO_CHAR (LMODE))          MODE_HELD,       DECODE (REQUEST,               0, ' None ',               1, ' Null ',               2, ' Row-S(SS) ',               3, ' Row-X(SX) ',               4, ' Share',               5, 'S/Row-X (SSX) ',               6, 'Exclusive ',               TO_CHAR (REQUEST))          MODE_REQUESTED,       O.OWNER || ' . ' || O.OBJECT_NAME || ' ( ' || O.OBJECT_TYPE || ' ) '          AS OBJECT_NAME,       S.TYPE LOCK_TYPE,       L.ID1 LOCK_ID1,       L.ID2 LOCK_ID2,       S2.SQL_TEXT  FROM V$LOCK L,       SYS.DBA_OBJECTS O,       V$SESSION S,       V$ACCESS A,       V$SQL S2 WHERE     L.SID = S.SID       AND L.ID1 = O.OBJECT_ID       AND S.SID = A.SID       AND S2.HASH_VALUE = S.SQL_HASH_VALUE       AND A.OBJECT = 'PROC_VALIDATE_RULE_V3';
效果如下：
 
 
 
 
 
 
-------------------------------------------------------------------------------------------------------
著作權，文章允許轉載，但必須以連結方式註明源地址，否則追究法律責任! 
Email: tianlesoftware@gmail.com
QQ:tianlesoftware@gmail.com
Skype: tianlesoftware
Blog:http://www.tianlesoftware.com
Weibo: http://weibo.com/tianlesoftware
Twitter: http://twitter.com/tianlesoftware
Facebook: http://www.facebook.com/tianlesoftware
Linkedin: http://cn.linkedin.com/in/tianlesoftware
-------加群需要在備忘說明Oracle資料表空間和資料檔案的關係，否則拒絕申請----
DBA1 群：62697716(滿);   DBA2 群：62697977(滿)   DBA3 群：62697850(滿)   
DBA 超級群：63306533(滿);  DBA4 群：83829929   DBA5群： 142216823 
DBA6 群：158654907    DBA7 群：172855474   DBA總群：104207940