Oracle view the lock held by the object

 

My colleague added a field to a table in the test database, prompting ORA-00054, resource busy. The table object lock is not released.

For instructions on Oracle locks, refer:

Oracle Lock Mechanism

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

 

Use the following SQL statement to check the locking status of related objects in the system:

 

/* 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';
 
 
This SQL statement displays the locks on all objects. If you want to query a specific object, you can perform Filtering Based on the object_name field. Find the corresponding Sid and check the V $ session view.
 
This view displays the session information, including terminal information. If a terminal is found, it can be submitted or rolled back. I am in the test environment and kill the session directly. Then modify the table.
 
SQL>alter system kill session 'SID,SERIAL#'
Later I tested and found that the above script sometimes could not find the relevant records of the object, so I added the V $ access view. By judging the object in this view, the modified script is as follows:
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';
I modified it again and added the V $ SQL view, so that I can find the SQL statement that causes the lock at one time. The SQL statement is as follows:
/* 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';
The effect is as follows:
 
 
 
 
 
 
Bytes -------------------------------------------------------------------------------------------------------
All rights reserved. reprinted articles are allowed, but source addresses must be indicated by links. Otherwise, the documents will be held legally responsible!
Email: tianlesoftware@gmail.com
QQ: tianlesoftware@gmail.com
Skype: tianlesoftware
Blog: http://www.tianlesoftware.com
WEAVER: http://weibo.com/tianlesoftware
Twitter: http://twitter.com/tianlesoftware
Facebook: http://www.facebook.com/tianlesoftware
LinkedIn: http://cn.linkedin.com/in/tianlesoftware
------- Add a group to describe the relationship between Oracle tablespace and data files in the remarks section. Otherwise, reject the application ----
Dba1 group: 62697716 (full); dba2 group: 62697977 (full) dba3 group: 62697850 (full)
Super DBA group: 63306533 (full); dba4 group: 83829929 dba5 group: 142216823
Dba6 group: 158654907 dba7 group: 172855474 DBA group: 104207940