oracle手动锁表和解锁_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