Oracle 中被锁定的表

查询oracle中被锁定的表

1
2
3
4
5
6
7
8
-- 相关表
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;

查询锁定的对象

查询数据库中锁定的对象

1
2
3
4
5
6
-- 可以根据操作系统的用户名查询
SELECT B.OWNER, B.OBJECT_NAME, A.SESSION_ID, A.LOCKED_MODE, A.PROCESS,
       A.OS_USER_NAME
  FROM V$LOCKED_OBJECT A, DBA_OBJECTS B
 WHERE B.OBJECT_ID = A.OBJECT_ID
   -- AND A.OS_USER_NAME = '操作系统用户名称';

查询Session和SERIAL#序号

1
2
3
4
5
6
-- SESSION_ID会话id
SELECT B.USERNAME, B.SID, B.SERIAL#, LOGON_TIME
  FROM V$LOCKED_OBJECT A, V$SESSION B
 WHERE A.SESSION_ID = B.SID
   AND A.SESSION_ID IN (404) -- 会话id
 ORDER BY B.LOGON_TIME;

杀死锁定表的会话(Session)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 查询被锁定的表
-- OS_USER_NAME操作系统用户名称
SELECT B.OWNER, B.OBJECT_NAME, A.SESSION_ID, A.LOCKED_MODE, A.PROCESS,
       A.OS_USER_NAME
  FROM V$LOCKED_OBJECT A, DBA_OBJECTS B
 WHERE B.OBJECT_ID = A.OBJECT_ID
   -- AND A.OS_USER_NAME = 'os';

-- 查询Session和SERIAL#序号
SELECT B.USERNAME, B.SID, B.SERIAL#, LOGON_TIME
  FROM V$LOCKED_OBJECT A, V$SESSION B
 WHERE A.SESSION_ID = B.SID
   AND A.SESSION_ID IN (404)
 ORDER BY B.LOGON_TIME;
 
 -- 根据Session和SERIAL#序号杀死锁定的会话
 ALTER SYSTEM KILL SESSION '404,6797';