--锁表几个关联表
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;
--查看被锁的表
select
b.owner,
b.object_name,
a.session_id,
a.locked_mode
from v$locked_object a,dba_objects b
where a.object_id = b.object_id;
--查看哪个用户 进程造成了死锁
select
b.username,
b.sid,
b.serial#,
b.logon_time
from v$locked_object a,v$session b
where a.session_id = b.sid
order by b.logon_time;
--查看连接进程
select sid,
serial#,
username,
osuser
from v$session;
--查出锁定表的sid,serial#,os_user_name,machine_name,terminal,锁的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;
--以上语句查询数据库中所有DML语句产生的锁，可以发现，其实产生了两个锁，一个是表锁，一个是行锁
--杀掉进程 sid,serial#
alter system kill session '210,11562';