1.先查询被锁的表，获取sessionID

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;

字段名
OWNER ：数据表的所有者用户
OBJECT_NAME： 被锁住的表名
SESSION_ID： 会话ID
LOCKED_MODE： 锁级别

锁级别分为6级
1级锁有：select
2级锁有：select for update,Lock For Update,Lock Row Share
3级锁有：insert, update, delete, Lock Row Exclusive
4级锁有：create Index, Lock Share
5级锁有：Lock Share Row Exclusive
6级锁有：Alter table, Drop table, Drop Index, Truncate table, Lock Exclusive

2.查询会话ID：serial#

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.logon_time;

3.杀死会话

alter system kill session 'sid,serial#';

sid:第一个sql的SESSION_ID的值
serial#:第二个sql的SERIAL#的值

4.如果要杀死多个会话可以使用这个办法

select 'alter system kill session '''||sess.sid||','||sess.serial#||''';'
from v$locked_object lo,
dba_objects ao,
v$session sess
where ao.object_id = lo.object_id and lo.session_id = sess.sid

查询出来的结果是需要执行的SQL语句，直接执行就可以了

5.杀死会话之后需要排查系统或数据库设置

排查是否是SQL语句太过冗余，操作耗时太长，对SQL进行优化
表中数据量太大，主键、外键、索引等是否添加，对表结构进行优化

6.锁表相关的表

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;

7.查看那个用户那个进程造成死锁

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.LOGON_TIME;

8.查看连接的进程

SELECT SID, SERIAL#, USERNAME, OSUSER FROM V$SESSION;

9.查出锁定表的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;

文章中引用的相关内容来自一下文章:
https://blog.csdn.net/lichongxyz/article/details/126851107
https://blog.csdn.net/weixin_44767040/article/details/124077699