If there is a lock waiting, we might be more interested in knowing who locked the table and who's waiting.
The following statement can query who locked the table:
Copy Code code as follows:
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 statements can be queried as to who is waiting:
Copy Code code 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 Code code as follows:
Alter system kill session ' sid,serial# '
1).
Copy Code code as follows:
Select Lock_info. OWNER | | '.' || Lock_info. Obj_name as "locked object name"--object name (already locked)
Lock_info. Subobj_name as "locked child object Name"--child object name (already locked)
Sess_info. MACHINE as "machine name",--machine name
Lock_info. session_id as "Call ID",--conversation session_id
Sess_info. serial# as "conversation serial#",--conversation serial#
Sess_info. SPID as "The OS system's SPID"--the OS system's SPID
(SELECT instance_name from v$instance) "Example name Sid"--The name SID
Lock_info. Ora_username as "Oracle User"--Oracle System user name
Lock_info. Os_username as "OS user"--Business System user name
Lock_info. Process as "process number"--progression number
Lock_info. OBJ_ID as "image ID"--Image ID
Lock_info. Obj_type as "The image type",--the image type
Sess_info. Logon_time as "Log Time"--Log time
Sess_info. Program as "Name of program"--Name
Sess_info. STATUS as "The state of conversation",--the state of conversation
Sess_info. Lockwait as "Wait for lock",--Wait for lock
Sess_info. ACTION as "Motion",--motion
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 Code code 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 Code code as follows:
ALTER SYSTEM KILL session ' phone ID, serial# ';
4).
kill-9 OS system's SPID
