服务器之家

服务器之家 > 正文

oracle查询锁表与解锁情况提供解决方案

时间:2019-11-13 15:40     来源/作者:oracle教程网

如果发生了锁等待,我们可能更想知道是谁锁了表而引起谁的等待 
以下的语句可以查询到谁锁了表: 

复制代码代码如下:

SELECT /*+ rule */ s.username, 
decode(l.type,'TM','TABLE LOCK', 
'TX','ROW LOCK', 
NULL) LOCK_LEVEL, 
o.owner,o.object_name,o.object_type, 
s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser 
FROM v$session s,v$lock l,dba_objects o 
WHERE l.sid = s.sid 
AND l.id1 = o.object_id(+) 
AND s.username is NOT NULL 


以下的语句可以查询到谁在等待: 

复制代码代码如下:

SELECT /*+ rule */ lpad(' ',decode(l.xidusn ,0,3,0))||l.oracle_username User_name, o.owner,o.object_name,o.object_type,s.sid,s.serial# 
FROM v$locked_object l,dba_objects o,v$session s 
WHERE l.object_id=o.object_id 
AND l.session_id=s.sid 
ORDER BY o.object_id,xidusn DESC 


解锁命令: 

复制代码代码如下:

alter system kill session 'sid,serial#' 


1). 

复制代码代码如下:

select LOCK_INFO.OWNER || '.' || LOCK_INFO.OBJ_NAME as "已鎖物件名稱", --物件名稱(已經被鎖住) 
LOCK_INFO.SUBOBJ_NAME as "已鎖子物件名稱", -- 子物件名稱(已經被鎖住) 
SESS_INFO.MACHINE as "機器名稱", -- 機器名稱 
LOCK_INFO.SESSION_ID as "會話ID", -- 會話SESSION_ID 
SESS_INFO.SERIAL# as "會話SERIAL#", -- 會話SERIAL# 
SESS_INFO.SPID as "OS系統的SPID", -- OS系統的SPID 
(SELECT INSTANCE_NAME FROM V$INSTANCE) "實例名SID", --實例名SID 
LOCK_INFO.ORA_USERNAME as "ORACLE用戶", -- ORACLE系統用戶名稱 
LOCK_INFO.OS_USERNAME as "OS用戶", -- 作業系統用戶名稱 
LOCK_INFO.PROCESS as "進程編號", -- 進程編號 
LOCK_INFO.OBJ_ID as "對象ID", -- 對象ID 
LOCK_INFO.OBJ_TYPE as "對象類型", -- 對象類型 
SESS_INFO.LOGON_TIME as "登錄時間", -- 登錄時間 
SESS_INFO.PROGRAM as "程式名稱", -- 程式名稱 
SESS_INFO.STATUS as "會話狀態", -- 會話狀態 
SESS_INFO.LOCKWAIT as "等待鎖", -- 等待鎖 
SESS_INFO.ACTION as "動作", -- 動作 
SESS_INFO.CLIENT_INFO as "客戶資訊" -- 客戶資訊 
from (select obj.OWNER as OWNER, 
obj.OBJECT_NAME as OBJ_NAME, 
obj.SUBOBJECT_NAME as SUBOBJ_NAME, 
obj.OBJECT_ID as OBJ_ID, 
obj.OBJECT_TYPE as OBJ_TYPE, 
lock_obj.SESSION_ID as SESSION_ID, 
lock_obj.ORACLE_USERNAME as ORA_USERNAME, 
lock_obj.OS_USER_NAME as OS_USERNAME, 
lock_obj.PROCESS as PROCESS 
from (select * 
from all_objects 
where object_id in (select object_id from v$locked_object)) obj, 
v$locked_object lock_obj 
where obj.object_id = lock_obj.object_id) LOCK_INFO, 
(select SID, 
SERIAL#, 
LOCKWAIT, 
STATUS, 
(select spid from v$process where addr = a.paddr) spid, 
PROGRAM, 
ACTION, 
CLIENT_INFO, 
LOGON_TIME, 
MACHINE 
from v$session a) SESS_INFO 
where LOCK_INFO.SESSION_ID = SESS_INFO.SID 
order by LOCK_INFO.SESSION_ID; 


2). 

复制代码代码如下:

select sql_text 
from v$sqltext 
where address in (select sql_address from v$session where sid = &sid) 
order by piece; 


3). 

复制代码代码如下:

ALTER SYSTEM KILL SESSION '會話ID,會話SERIAL#'; 


4). 
kill -9 OS系統的SPID

标签:

相关文章

热门资讯

玄元剑仙肉身有什么用 玄元剑仙肉身境界等级划分
玄元剑仙肉身有什么用 玄元剑仙肉身境界等级划分 2019-06-21
男生常说24816是什么意思?女生说13579是什么意思?
男生常说24816是什么意思?女生说13579是什么意思? 2019-09-17
配置IIS网站web服务器的安全策略配置解决方案
配置IIS网站web服务器的安全策略配置解决方案 2019-05-23
Nginx服务器究竟是怎么执行PHP项目
Nginx服务器究竟是怎么执行PHP项目 2019-05-24
华为nova5pro和p30pro哪个好 华为nova5pro和华为p30pro对比详情
华为nova5pro和p30pro哪个好 华为nova5pro和华为p30pro对比详情 2019-06-22
返回顶部