锁占用相关
*****Oracle查锁
SELECT L.SESSION_ID "会话ID",
S.SERIAL# "会话序列号",
P.SPID "会话进程号",
S.USERNAME "所属用户",
S.MACHINE "客户端",
O.OBJECT_NAME "被锁对象",
O.OBJECT_TYPE "被锁对象类型",
CEIL((SYSDATE - S.LOGON_TIME) * 24 * 60 * 60) "被锁时间(S)"
FROM V$LOCKED_OBJECT L, V$SESSION S, DBA_OBJECTS O, V$PROCESS P
WHERE L.SESSION_ID = S.SID
AND L.OBJECT_ID = O.OBJECT_ID
AND S.PADDR = P.ADDR
AND S.SCHEMA# <> 0
AND S.USERNAME = 'GANGMA2';
杀会话
alter system kill session 'SID,SERIAL#';
查看被锁（占用）的表的sid
select SESS.sid, SESS.SERIAL#, LO.ORACLE_USERNAME, LO.OS_USER_NAME,
AO.OBJECT_NAME, LO.LOCKED_MODE, SESS.SQL_id
from V$LOCKED_OBJECT LO, DBA_OBJECTs AO, V$SESSION SESS
where AO.OBJECT_ID = LO.OBJECT_ID
and lo.session_id = sess.sid;
杀掉会话
alter system kill session '173,16112';
查看等待
SELECT sid,
username,
event,
blocking_session,
seconds_in_wait,
wait_time,
SQL_id
FROM v$session
WHERE state IN ('WAITING') AND wait_class <> 'Idle';
select sid,type,id1,id2,lmoderequest,block from v$lock where type in ('TX','TM') order by sid;
--block为1则堵塞，0为未堵塞
图片.png
1. 首先查看数据库中哪些表被锁了，找到session ID：
使用sql：
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
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#';
例子：
alter system kill session '20,30153'; -----这个是我刚才杀死会话前的执行语句
如果有ora-00031错误，则在后面加immediate;
alter system kill session '20,30153' immediate;
还有一种简便快捷办法
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
