【Oracle】—— 锁表查询与解锁

更新日志：

• [2024-08-19]
• 改进 批量生成查杀语句 的SQL语句，使得结果没有重复。
• [2020-09-10]
  • 添加 生成特定用户的批量查杀语句 的语句
• [ 2020-06-28 ]
  • 修改批量查杀中的书写错误，SERIAL #改为 SERIAL#
• [2019-12-30]
  • 添加批量查杀生成语句
• [2019-11-22]
  • 添加查杀session时对错误ORA-00031: session marked for kill的处理方法
  • 调整整体样式，更整洁、严谨。
• [2019-10-09]
  • 添加关于多节点Oracle被锁住表的查杀的说明
• [2019-09-30]
  • 文章发布

说明：

• 本文地址
  • 《【Oracle】—— 锁表查询与解锁》https://blog.csdn.net/maixiaochai/article/details/101757305
• 关于 MaiXiaochai
  • CSDN：https://blog.csdn.net/maixiaochai
  • GitHub：https://github.com/MaiXiaochai

---

1 查询被锁住的表

•   -- 查询被锁表信息
    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;
  

  • 单行版， 便于在sqlplus中使用

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

2 通过上一步的session_id查询进程

•   -- 查询进程具体信息
    SELECT
    	* 
    FROM
    	V$SESSION T1,
    	V$LOCKED_OBJECT T2 
    WHERE
    	T1.SID = T2.SESSION_ID 
    	AND T1.SID = '5077';
  

• 单行版，便于在 sqlplus中使用

  SELECT * FROM V$SESSION T1,V$LOCKED_OBJECT T2 WHERE T1.SID = T2.SESSION_ID AND T1.SID = '5077';
  

  

3 处理占用表的进程

• 单条查杀语句

  -- 杀死锁表进程
  -- DBA权限
  -- 后边的两个值分别是'SID, SERIAL#'，注意在引号里边
  -- 如果Oracle有多个节点，要在相应的节点上运行。具体信息可以在 2 中的查询结果中得到。
  ALTER SYSTEM KILL SESSION '5077,355';
  

• 生成批量查杀语句

  SELECT DISTINCT
  	'ALTER SYSTEM KILL SESSION ''' || SID || ',' || SERIAL# || ''';' 
  FROM
  	(
  	SELECT DISTINCT
  		SID,
  		SERIAL#
  	FROM
  		V$SESSION T1,
  		V$LOCKED_OBJECT T2 
  	WHERE
  		T1.SID = T2.SESSION_ID 
  	AND T1.SID IN ( SELECT A.SESSION_ID 
  					FROM V$LOCKED_OBJECT A, DBA_OBJECTS B
  					WHERE B.OBJECT_ID = A.OBJECT_ID ) 
  	);
  -- 生成之后，复制到Oracle工具，执行即可。
  

• 若杀死session时报错ORA-00031: session marked for kill，则继续看步骤4

• 生成特定用户的批量查杀语句

  SELECT
  distinct	'ALTER SYSTEM KILL SESSION ''' || SID || ',' || SERIAL# || ''';' 
  FROM
  	(
  	SELECT
  		SID,
  		SERIAL#
  	FROM
  		V$SESSION T1,
  		V$LOCKED_OBJECT T2 
  	WHERE
  		T1.SID = T2.SESSION_ID 
  	AND T1.SID IN ( SELECT A.SESSION_ID 
  					FROM V$LOCKED_OBJECT A, DBA_OBJECTS B
  					WHERE B.OBJECT_ID = A.OBJECT_ID and b.owner='the_user_name') 
  	);
  

4. 杀session报错ORA-00031: session marked for kill

•   -- 5077 为 步骤3中
    SELECT
    	SPID,
    	OSUSER,
    	S.PROGRAM 
    FROM
    	V$SESSION S,
    	V$PROCESS P 
    WHERE
    	S.PADDR = P.ADDR 
    	AND S.SID = 5077;
  

  获得spid 如666,需要在杀系统进程

  -- linux
  kill -9 666
  
  -- windows
  orakill orcl 666
  

The end.