oracle provides solutions for querying lock tables and unlocking situations

If a lock wait occurs, we may be more interested in knowing who locked the table and caused whose wait
The following statement can be used to query who has locked the table:

 
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 

The following statement can query who is waiting:

 
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 

Unlock command:

 
alter system kill session 'sid,serial#' 

1).

 
select LOCK_INFO.OWNER || '.' || LOCK_INFO.OBJ_NAME as " The object name has been invoked ", -- Object name � ( Already occupied ) 
LOCK_INFO.SUBOBJ_NAME as " Child object names have been acquired ", --  Child object names ( Already occupied ) 
SESS_INFO.MACHINE as " � � device name ", --  � � device name  
LOCK_INFO.SESSION_ID as " � � ID", --  � � SESSION_ID 
SESS_INFO.SERIAL# as " � � SERIAL#", --  � � SERIAL# 
SESS_INFO.SPID as "OS Department of � SPID", -- OS Department of � SPID 
(SELECT INSTANCE_NAME FROM V$INSTANCE) " � case name SID", -- � case name SID 
LOCK_INFO.ORA_USERNAME as "ORACLE With � ", -- ORACLE By name  
LOCK_INFO.OS_USERNAME as "OS With � ", --  In the form and in the name  
LOCK_INFO.PROCESS as " � cheng � � ", --  � cheng � �  
LOCK_INFO.OBJ_ID as " � like ID", --  � like ID 
LOCK_INFO.OBJ_TYPE as " � � type ", --  � � type  
SESS_INFO.LOGON_TIME as " Deng � � � ", --  Deng � � �  
SESS_INFO.PROGRAM as " The program name � ", --  The program name �  
SESS_INFO.STATUS as " � � � � ", --  � � � �  
SESS_INFO.LOCKWAIT as " Waiting for � ", --  Waiting for �  
SESS_INFO.ACTION as " � a ", --  � a  
SESS_INFO.CLIENT_INFO as " Guest � � � " --  Guest � � �  
from (select obj.OWNER as OWNER, 
obj.OBJECT_NAME as OBJ_NAME, 
obj.SUBOBJECT_NAME as SUBOBJ_NAME, 
obj.OBJECT_ID as OBJ_ID, 
obj.OBJECT_TYPE as OBJ_TYPE, 
lock_obj.SESSION_ID as SESSION_ID, 
lock_obj.ORACLE_USERNAME as ORA_USERNAME, 
lock_obj.OS_USER_NAME as OS_USERNAME, 
lock_obj.PROCESS as PROCESS 
from (select * 
from all_objects 
where object_id in (select object_id from v$locked_object)) obj, 
v$locked_object lock_obj 
where obj.object_id = lock_obj.object_id) LOCK_INFO, 
(select SID, 
SERIAL#, 
LOCKWAIT, 
STATUS, 
(select spid from v$process where addr = a.paddr) spid, 
PROGRAM, 
ACTION, 
CLIENT_INFO, 
LOGON_TIME, 
MACHINE 
from v$session a) SESS_INFO 
where LOCK_INFO.SESSION_ID = SESS_INFO.SID 
order by LOCK_INFO.SESSION_ID; 

2).

 
select sql_text 
from v$sqltext 
where address in (select sql_address from v$session where sid = &sid) 
order by piece; 

3).

 
ALTER SYSTEM KILL SESSION ' � � ID, � � SERIAL#'; 

4).
kill-9 OS is derived from SPID