Oracle lock table and unlock

Oracle lock table and unlock

Select/* + rule */S. username,
Decode (L. type, 'TT', 'table lock ',
'Tx ', 'row lock ',
Null) lock_level,
O. Owner, O. object_name, O. object_type,
S. Sid, S. Serial #, S. Terminal, S. Machine, S. Program, S. osuser
From v $ session S, V $ lock l, dba_objects o
Where l. Sid = S. Sid
And l. id1 = O. object_id (+)
And S. username is not null

-- Kill session Statement
Alter system kill session '123 ';
-- The following are related tables:
Select * from V $ lock;
Select * from V $ sqlarea;
Select * from V $ session;
Select * from V $ process;
Select * from V $ locked_object;
Select * From all_objects;
Select * from V $ session_wait;
-- 1. Check the session information of the locked object and the name of the locked object.
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 #;
-- 2. Locate the SID, serial #, OS _user_name, machine name, terminal, and executed STATEMENT OF THE SESSION OF THE locked table.
-- More SQL _text and action than above
Select L. session_id Sid, S. Serial #, L. locked_mode, L. oracle_username, S. User #,
L. OS _user_name, S. Machine, S. Terminal, A. SQL _text, A. Action
From v $ sqlarea A, V $ session S, V $ locked_object L
Where l. session_id = S. Sid
And S. prev_ SQL _addr = A. Address
Order by SID, S. Serial #;
-- 3. Identify the SID, serial #, OS _user_name, machine_name, terminal, type, mode of the lock.
Select S. Sid, S. Serial #, S. username, S. schemaname, S. osuser, S. process, S. machine,
S. Terminal, S. logon_time, L. Type
From v $ session S, V $ lock l
Where S. Sid = L. Sid
And S. username is not null
Order by SID;

This statement finds the locks generated by all the DML statements in the database,
Any DML statement actually produces two locks: one is the table lock and the other is the row lock.
Lock kill command
Alter system kill session 'sid, serial #'
Select/* + rule */S. username,
Decode (L. type, 'TT', 'table lock ',
'Tx ', 'row lock ',
Null) lock_level,
O. Owner, O. object_name, O. object_type,
S. Sid, S. Serial #, S. Terminal, S. Machine, S. Program, S. osuser
From v $ session S, V $ lock l, dba_objects o
Where l. Sid = S. Sid
And l. id1 = O. object_id (+)
And S. username is not null
If a lock wait occurs, we may want to know who has locked the table and who has waited.
The following statement can be used to query who has locked the table and who is waiting.
The preceding query result is a tree structure. If a subnode exists, it indicates that a wait occurs.
If you want to know which rollback segment the lock uses, you can also associate it with V $ rollname. xidusn is the USN of the rollback segment.
Col user_name format A10
Col owner format A10
Col object_name format A10
Col object_type format A10
Select/* + rule */lpad ('', decode (L. xidusn, 0, 3, 0) | L. oracle_username user_name,
O. Owner, O. object_name, O. object_type, S. Sid, S. Serial #
From v $ locked_object L, dba_objects o, V $ session s
Where l. object_id = O. object_id
And l. session_id = S. Sid
Order by O. object_id, xidusn DESC