Oracle locked table management

For various reasons, Oracle often suffers from Table locking, making operations impossible.

First, the System View related to the table lock:

A: V $ lock

SQL> DESC v $ lock;
Name type nullable default comments
-----------------------------------------
ADDR raw (8) y
Kaddr raw (8) y
SID number y
Type varchar2 (2) y
Id1 number y
Id2 number y
Lmode number y
Request number y
Ctime number y
Block number y

This view describes all the objects currently locked, the SID numbers of all locked objects, and the lock type;

B: V $ locked_object

SQL> DESC v $ locked_object;
Name type nullable default comments
--------------------------------------------------
Xidusn number y
Xidslot number y
Xidsqn number y
Object_id number y
Session_id number y
Oracle_username varchar2 (30) y
OS _user_name varchar2 (30) y
Process varchar2 (12) y
Locked_mode number y

It describes in detail the details of the currently locked object. object_id indicates the Object ID, session_id indicates the session number of the currently logged-on user, oracle_username indicates the username of Oracle, and OS _user_name indicates the operating system username.

For the above two system views, there are two ways to query the current table lock:

SQL> select all_objects.object_name,
2 S. Sid,
3 S. Serial #,
4 S. osuser,
5 S. program,
6 s. machine,
7 S. client_info
8 from V $ lock K, V $ session S, all_objects
9 where K. Sid = S. Sid
10 and K. Type in ('tx ', 'Tm ')
11 and K. id1 = all_objects.object_id;

Object_name Sid serial # osuser program machine client_info
---------------------------------------------------------
Dl_port 153 2 administrator mdiapp.exe WN-WHZ/guanxian1 135.135.140.101
Dl_local_port 153 2 administrator mdiapp.exe WN-WHZ/guanxian1 135.135.140.101

This method is inefficient.

Second:

SQL> select O. object_name,
2 L. session_id,
3 S. Serial #,
4 S. program,
5s. username,
6 s. Command,
7 S. machine,
8 S. lockwait
9 from V $ locked_object L, all_objects o, V $ session s
10 where O. object_id = L. object_id
11 and S. Sid = L. session_id
12;

Object_name session_id serial # program username command machine lockwait
---------------------------------------------------------------------
Dl_local_port 190 3 mdiapp.exe gsgis 0 workgroup/qygis19
Dl_port 190 3 mdiapp.exe gsgis 0 workgroup/qygis19