Oracle Database Query lock table and unlock statement

Lock tables are a common feature in many databases. I will introduce some examples of query lock tables and unlock statements in oracle. Thank you for your reference.

Query lock table statements

The Code is as follows:

Copy code

Select a. OWNER solution name, A. OBJECT_NAME table name, B. XIDUSN rollback segment number, B. XIDSLOT slot number, B. XIDSQN serial number, B. SESSION_ID lock table SESSION_ID, B. username of the ORACLE_USERNAME lock table, Decode (D. type, 'Xr ', 'Null ', 'Rs ', 'Ss (Row-S )', 'Cf ', 'Ss (Row-S )', 'TT ', 'Table lock ', 'PW ', 'Table lock ', 'To ', 'Table lock ', 'Ts ', 'Table lock ', 'Rt ', 'Row lock ', 'Tx ', 'Row lock ', 'Mr ', 'S (Share )', NULL) Locking method, C. MACHINE user group, C. TERMINAL machine name, B. OS _USER_NAME system username, B. PROCESS system PROCESS id, DECODE (C. STATUS, 'inactivity', 'inactivity', 'activity', 'activity') activity STATUS, C. SERVER, C. SID, C. SERIAL #, C. PROGRAM connection method, C. LOGON_TIME FROM ALL_OBJECTS A, V $ LOCKED_OBJECT B, SYS. GV _ $ SESSION C, v $ lock d WHERE (A. OBJECT_ID = B. OBJECT_ID) AND (B. PROCESS = C. PROCESS) And C. sid = d. sid And B. LOCKED_MODE = D. LMODE Order by 1, 2;

Example 2

Query locked tables:

The Code is as follows:	Copy code
SELECT l. session_id sid, s. serial #, l. locked_mode, l. oracle_username, L. OS _user_name, s. machine, s. terminal, o. object_name, s. logon_time FROM v $ locked_object l, all_objects o, v $ session s WHERE l. object_id = o. object_id AND l. session_id = s. sid Order by sid, s. serial #;

 

Delete the record locked by the system.

Example 1

The Code is as follows:	Copy code
ALTER system KILL session 'sid, serial #';

If there are multiple sid and serial # Delete each record repeatedly