Oracle View Object Hold lock condition

Colleagues in the test library on a table plus fields, prompting ORA-00054, resource busy. The lock on the Table object should not be released.

For a description of the Oracle lock, refer to:

Oracle lock mechanism

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

Use the following SQL to look at the locking of related objects in the system:

[SQL]View PlainCopy

2. <pre name="code" class="SQL" >/* formatted on 2012/2/13 14:24:32 (QP5 v5.185.11230.41888) */ /c5>
4. SELECT S.sid session_id,
6. S.username,
8. DECODE (Lmode,
10. 0, ' None ',
12. 1, ' Null ',
14. 2, ' Row-s (SS) ',
16. 3, ' Row-x (SX) ',
18. 4, ' Share ',
20. 5, ' S/row-x (SSX) ',
22. 6, ' Exclusive ',
24. To_char (Lmode))
26. Mode_held,
28. DECODE (REQUEST,
30. 0, ' None ',
32. 1, ' Null ',
34. 2, ' Row-s (SS) ',
36. 3, ' Row-x (SX) ',
38. 4, ' Share ',
40. 5, ' S/row-x (SSX) ',
42. 6, ' Exclusive ',
44. To_char (REQUEST))
46. Mode_requested,
48. O.owner | | '. ' | | O.object_name | | ' (' | | O.object_type | | ') '
50. as object_name,
52. S.type Lock_type,
54. L.id1 Lock_id1,
56. L.id2 Lock_id2
58. From V$lock L, SYS. Dba_objects O, V$session S
60. WHERE l.sid = s.sid and l.id1 = o.object_id and object_name = ' xxxx ';</pre><br>

The SQL shows the lock on all objects, if you want to find a specific object, you can filter according to the object_name field, find the corresponding SID and then check the V$session view.

This view displays the information for the session, including the terminal information, and if the terminal is found, it can be submitted or rolled back to OK. I'm here to test the environment and kill the session directly. Then modify the table to be OK.

[SQL]View PlainCopy

1. sql>alter system kill session ' sid,serial# '

Later, the test found that the above script sometimes can not find the relevant records of the object, so added the V$access view, through the view of the object to judge, after the modified script as follows:

[SQL]View PlainCopy

2. SELECT S.sid session_id,
4. S.username,
6. DECODE (Lmode,
8. 0, ' None ',
10. 1, ' Null ',
12. 2, ' Row-s (SS) ',
14. 3, ' Row-x (SX) ',
16. 4, ' Share ',
18. 5, ' S/row-x (SSX) ',
20. 6, ' Exclusive ',
22. To_char (Lmode))
24. Mode_held,
26. DECODE (REQUEST,
28. 0, ' None ',
30. 1, ' Null ',
32. 2, ' Row-s (SS) ',
34. 3, ' Row-x (SX) ',
36. 4, ' Share ',
38. 5, ' S/row-x (SSX) ',
40. 6, ' Exclusive ',
42. To_char (REQUEST))
44. Mode_requested,
46. O.owner | | '. ' | | O.object_name | | ' (' | | O.object_type | | ') '
48. as object_name,
50. S.type Lock_type,
52. L.id1 Lock_id1,
54. L.id2 Lock_id2
56. From V$lock L, SYS. Dba_objects O, v$session s,v$access A
58. WHERE l.sid = s.sid and l.id1 = o.object_id and s.sid=a.sid and a.object= ' proc_validate_rule_v3 ';

Once again, add the V$sql view, so that you can find the specific result of this kind of lock SQL statement, a one-time fix, SQL as follows:

[SQL]View PlainCopy

2. /* Formatted on 2012/6/6 10:59:49 (QP5 v5.185.11230.41888) */
4. SELECT distinct s.sid session_id,
6. S.status,
8. S.username,
10. DECODE (Lmode,
12. 0, ' None ',
14. 1, ' Null ',
16. 2, ' Row-s (SS) ',
18. 3, ' Row-x (SX) ',
20. 4, ' Share ',
22. 5, ' S/row-x (SSX) ',
24. 6, ' Exclusive ',
26. To_char (Lmode))
28. Mode_held,
30. DECODE (REQUEST,
32. 0, ' None ',
34. 1, ' Null ',
36. 2, ' Row-s (SS) ',
38. 3, ' Row-x (SX) ',
40. 4, ' Share ',
42. 5, ' S/row-x (SSX) ',
44. 6, ' Exclusive ',
46. To_char (REQUEST))
48. Mode_requested,
50. O.owner | | '. ' | | O.object_name | | ' (' | | O.object_type | | ') '
52. as object_name,
54. S.type Lock_type,
56. L.id1 Lock_id1,
58. L.id2 Lock_id2,
60. S2. Sql_text
62. From V$lock L,
64. SYS. Dba_objects O,
66. V$session S,
68. V$access A,
70. V$sql S2
72. WHERE l.sid = S.sid
74. and l.id1 = o.object_id
76. and S.sid = A.sid
78. and S2. Hash_value = S.sql_hash_value
80. and a.object = ' Proc_validate_rule_v3 ';

The effect is as follows:

Ext.: http://blog.csdn.net/tianlesoftware/article/details/6822321

Oracle View Object Hold lock condition