Solution for oracle to query the lock table and unlock situation

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:
Copy codeThe Code is as follows:
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

The following statement can be used to query who is waiting:
Copy codeThe Code is as follows:
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:
Copy codeThe Code is as follows:
Alter system kill session 'sid, serial #'

1 ).
Copy codeThe Code is as follows:
Select LOCK_INFO.OWNER | '.' | LOCK_INFO.OBJ_NAME as "existing object name already", -- object name already (already pinned)
LOCK_INFO.SUBOBJ_NAME as "subitem name already exists", -- subitem name already exists (already pinned)
SESS_INFO.MACHINE as "" ", --
LOCK_INFO.SESSION_ID as "session ID", -- session SESSION_ID
SESS_INFO.SERIAL # as "session SERIAL #", -- session SERIAL #
SESS_INFO.SPID as "OS system SPID", -- OS system SPID
(SELECT INSTANCE_NAME from v $ INSTANCE) "INSTANCE name SID", -- INSTANCE name SID
LOCK_INFO.ORA_USERNAME as "ORACLE users", -- region name for ORACLE Systems
LOCK_INFO. OS _USERNAME as "OS user", -- guest name used for operating systems
LOCK_INFO.PROCESS as "progress parameter", -- Progress Parameter
LOCK_INFO.OBJ_ID as "Object ID", -- Object ID
LOCK_INFO.OBJ_TYPE as "Object Type", -- Object Type
SESS_INFO.LOGON_TIME as "login time", -- login time
SESS_INFO.PROGRAM as "program name example", -- program name example
SESS_INFO.STATUS as "when", -- when "when"
SESS_INFO.LOCKWAIT as "waiting for renewal", -- waiting for renewal
SESS_INFO.ACTION as "action", -- Action
SESS_INFO.CLIENT_INFO as "customer information" -- customer information
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 ).
Copy codeThe Code is as follows:
Select SQL _text
From v $ sqltext
Where address in (select SQL _address from v $ session where sid = & sid)
Order by piece;

3 ).
Copy codeThe Code is as follows:
Alter system kill session 'session ID, session serial #';

4 ).
SPID of the kill-9 OS system