The V $ LOCK view contains information about the lock
V $ locked_object contains information about the lock object
For example: First use the DEMO user to log in in a session, then execute
Update Lunar Set C1 = 'First Lock' Where C2 = 999;
system shows:
SQL> UPDATE LUNAR SET C1 = 'First Lock' Where C2 = 999;
1 line has been updated.
Time: 00: 00: 00.00
SQL>
This session is not submitted, and then in another session, use Demo to log in, and then execute:
Update Lunar Set C1 = 'First Lock' Where C2 = 999;
At this time, this session will be in the status of the IDEL, that is, he is waiting for the exclusive lock of these rows in Waraday Lunar; then open a new session, use Demo to log in, and still execute:
Update Lunar Set C1 = 'First Lock' Where C2 = 999;
At this time, this session will also be in the status of the IDEL, and he is also waiting for the exclusive lock of these lines in C2 = 999, as shown in the figure:
Use sysdba to log in and perform the following script:
SQL> SELECT DECODE (Request, 0, 'Holder:', 'Waiter:') || SID Sess, ID1, ID2, LMODE,
2 Request, Type
3 from V $ LOCK
4 WHERE (ID1, ID2, TYPE) IN (SELECT ID1, ID2, TYPE FROM V $ LOCK WHERE Request> 0)
5 ORDER BY ID1, REQUEST
6 /
SESS ID1 ID2 LMODE REQUEST TYPE
----------------------------------- ------- ----
Holder: 12 393247 473 6 0 TX
Waiter: 8 393247 473 0 6 TX
Waiter: 16 393247 473 0 6 TX
SQL>
Holder means that the process holds the lock, Waiter means the process of waiting to be locked, so we need to find the Holder process, then find the session information according to the Holder SID, determine whether the user session (instead of a system session):
SQL> SELECT SID, Serial #, SQL_HASH_VALUE, Username, Type, Program, SchemanAme from v $ session
2 where sid = 12
3 /
SID Serial # SQL_HASH_VALUE Username Type Program Schemaname
----- -------- -------------------------------- --- ------------------------ 12 11 0 Demo User Sqlplus.exe Demo
SQL>
Note that if the value of SQL_HASH_VALUE is not 0, then the SQL is still running, finding this SQL statement with "Find the SQL statement" according to Hash Value "as mentioned in the previous section 7.5.
We can confirm that this lock is an Oracle user who is Demo by a model name (can be approximated as user name), of course, can also determine the type of the process (instead of a system process). Next, we can kill this SID.
If you want to see the object locked by the user, you can use V $ located_Object
SQL> SELECT Object_ID, session_id, oracle_username, process, locked_mode
2 from v $ locked_object;
Object_id session_id oracle_username process locked_mode
---------- ---------------------------------------------------------------------------------------------------------------- ------------ -----------
7382 8 Demo 2516: 1812 3
7382 12 Demo 2440: 2424 3
7382 16 Demo 2524: 2384 3
SQL>
Then use the ALTER System Kill Session to kill this process, for example:
SQL> ALTER SYSTEM KILL session '12, 11 ';
SYSTEM altered
SQL>
