如果发生了锁等待，我们可能更想知道是谁锁了表而引起谁的等待 @H_403_1@以下的语句可以查询到谁锁了表: @H_403_1@
代码如下:
403_1@SELECT /*+ rule */ s.username,@H_403_1@decode(l.type,'TM','TABLE LOCK',@H_403_1@'TX','ROW LOCK',@H_403_1@NULL) LOCK_LEVEL,@H_403_1@o.owner,o.object_name,o.object_type,@H_403_1@s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser @H_403_1@FROM v$session s,v$lock l,dba_objects o @H_403_1@WHERE l.sid = s.sid @H_403_1@AND l.id1 = o.object_id(+) @H_403_1@AND s.username is NOT NULL @H_403_1@
@H_403_1@以下的语句可以查询到谁在等待: @H_403_1@ 代码如下:
403_1@SELECT /*+ rule */ lpad(' ',decode(l.xidusn,3,0))||l.oracle_username User_name,o.owner,s.sid,s.serial# @H_403_1@FROM v$locked_object l,dba_objects o,v$session s @H_403_1@WHERE l.object_id=o.object_id @H_403_1@AND l.session_id=s.sid @H_403_1@ORDER BY o.object_id,xidusn DESC @H_403_1@
@H_403_1@解锁命令: @H_403_1@ 代码如下:
403_1@alter system kill session 'sid,serial#' @H_403_1@
@H_403_1@1). @H_403_1@ 代码如下:
403_1@select LOCK_INFO.OWNER || '.' || LOCK_INFO.OBJ_NAME as "已鎖物件名稱",--物件名稱(已經被鎖住) @H_403_1@LOCK_INFO.SUBOBJ_NAME as "已鎖子物件名稱",-- 子物件名稱(已經被鎖住) @H_403_1@SESS_INFO.MACHINE as "機器名稱",-- 機器名稱 @H_403_1@LOCK_INFO.SESSION_ID as "會話ID",-- 會話SESSION_ID @H_403_1@SESS_INFO.SERIAL# as "會話SERIAL#",-- 會話SERIAL# @H_403_1@SESS_INFO.SPID as "OS系統的SPID",-- OS系統的SPID @H_403_1@(SELECT INSTANCE_NAME FROM V$INSTANCE) "實例名SID",--實例名SID @H_403_1@LOCK_INFO.ORA_USERNAME as "ORACLE用戶",-- ORACLE系統用戶名稱 @H_403_1@LOCK_INFO.OS_USERNAME as "OS用戶",-- 作業系統用戶名稱 @H_403_1@LOCK_INFO.PROCESS as "進程編號",-- 進程編號 @H_403_1@LOCK_INFO.OBJ_ID as "對象ID",-- 對象ID @H_403_1@LOCK_INFO.OBJ_TYPE as "對象類型",-- 對象類型 @H_403_1@SESS_INFO.logoN_TIME as "登錄時間",-- 登錄時間 @H_403_1@SESS_INFO.PROGRAM as "程式名稱",-- 程式名稱 @H_403_1@SESS_INFO.STATUS as "會話狀態",-- 會話狀態 @H_403_1@SESS_INFO.LOCKWAIT as "等待鎖",-- 等待鎖 @H_403_1@SESS_INFO.ACTION as "動作",-- 動作 @H_403_1@SESS_INFO.CLIENT_INFO as "客戶資訊" -- 客戶資訊 @H_403_1@from (select obj.OWNER as OWNER,@H_403_1@obj.OBJECT_NAME as OBJ_NAME,@H_403_1@obj.SUBOBJECT_NAME as SUBOBJ_NAME,@H_403_1@obj.OBJECT_ID as OBJ_ID,@H_403_1@obj.OBJECT_TYPE as OBJ_TYPE,@H_403_1@lock_obj.SESSION_ID as SESSION_ID,@H_403_1@lock_obj.ORACLE_USERNAME as ORA_USERNAME,@H_403_1@lock_obj.OS_USER_NAME as OS_USERNAME,@H_403_1@lock_obj.PROCESS as PROCESS @H_403_1@from (select * @H_403_1@from all_objects @H_403_1@where object_id in (select object_id from v$locked_object)) obj,@H_403_1@v$locked_object lock_obj @H_403_1@where obj.object_id = lock_obj.object_id) LOCK_INFO,@H_403_1@(select SID,@H_403_1@SERIAL#,@H_403_1@LOCKWAIT,@H_403_1@STATUS,@H_403_1@(select spid from v$process where addr = a.paddr) spid,@H_403_1@PROGRAM,@H_403_1@ACTION,@H_403_1@CLIENT_INFO,@H_403_1@logoN_TIME,@H_403_1@MACHINE @H_403_1@from v$session a) SESS_INFO @H_403_1@where LOCK_INFO.SESSION_ID = SESS_INFO.SID @H_403_1@order by LOCK_INFO.SESSION_ID; @H_403_1@
@H_403_1@2). @H_403_1@ 代码如下:
403_1@select sql_text @H_403_1@from v$sqltext @H_403_1@where address in (select sql_address from v$session where sid = &sid) @H_403_1@order by piece; @H_403_1@
@H_403_1@3). @H_403_1@ 代码如下:
403_1@ALTER SYSTEM KILL SESSION '會話ID,會話SERIAL#'; @H_403_1@
@H_403_1@4). @H_403_1@kill -9 OS系統的SPID