oracle手动锁表和解锁_oracle查询锁表与解锁情况提供解决方案

柔情只为你懂 2023-01-01 06:52 85阅读 0赞

如果发生了锁等待，我们可能更想知道是谁锁了表而引起谁的等待

以下的语句可以查询到谁锁了表:

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

oracle手动锁表和解锁_oracle查询锁表与解锁情况提供解决方案

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 oracle手动锁表和解锁_oracle查询锁表与解锁情况提供解决方案

相关 Oracle锁表与解锁

相关 oracle 查看锁表和解锁

相关 Oracle的锁表与解锁

相关 oracle-查看锁表和解锁

相关 Oracle的锁表与解锁

相关 oracle锁表，解锁

相关 Oracle应用之锁表和解锁脚本

相关 Oracle的锁表与解锁、Oracle锁用户

相关 oracle 查询被锁表和解锁