Oracle's lock table and unlock

Oracle's Lock table and unlock

SELECT/*+ Rule */s.username,
Decode (L.type, ' TM ', ' 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 ' 50,492 ';
--The following are the 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. Find information about the session that locked the object and the object name that was locked
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. The SID, Serial#,os_user_name, machine name, terminal, and executed statements of the session in which the lock table is isolated
-- more sql_text and action than the above paragraph
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. SID of the locked table, Serial#,os_user_name, machine_name, terminal, lock Type,mode
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 will find the locks generated by all DML statements in the database, and can also be found
Any DML statement actually produces two locks, one is a table lock and one is a row lock.
Kill lock Command
Alter system kill session ' sid,serial# '
SELECT/*+ Rule */s.username,
Decode (L.type, ' TM ', ' 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 there's a lock waiting, we might want to know who locked the table and who's waiting.
The following statement can query who locked the table and who is waiting.
The above query result is a tree structure, and if there are child nodes, it indicates that there is a wait to occur.
If you want to know which rollback segment the lock uses, you can also associate to V$rollname, where 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

Oracle's lock table and unlock