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