--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;
--View the locked table
Select B.owner,b.object_name,a.session_id,a.locked_mode from V$locked_object a,dba_objects b where b.object_id = a.object_id;
--View the user that process as a deadlock
Select B.username,b.sid,b.serial#,logon_time from V$locked_object a,v$session b where a.session_id = B.sid ORDER by B.logo N_time;
--View the connected process
SELECT SID, Serial#, username, osuser from v$session;
--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 the process sid,serial#
Alter system kill session ' 137,13662 ';
170308. Oracle View locked table and unlock