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 "已i物件名Q", --物件名Q(已被i住) LOCK_INFO.SUBOBJ_NAME as "已i子物件名Q", -- 子物件名Q(已被i住) SESS_INFO.MACHINE as "C器名Q", -- C器名Q LOCK_INFO.SESSION_ID as "ID", -- SESSION_ID SESS_INFO.SERIAL# as "SERIAL#", -- SERIAL# SESS_INFO.SPID as "OS系y的SPID", -- OS系y的SPID (SELECT INSTANCE_NAME FROM V$INSTANCE) "例名SID", --例名SID LOCK_INFO.ORA_USERNAME as "ORACLE用", -- ORACLE系y用裘Q LOCK_INFO.OS_USERNAME as "OS用", -- 作I系y用裘Q LOCK_INFO.PROCESS as "M程", -- M程 LOCK_INFO.OBJ_ID as "οID", -- οID LOCK_INFO.OBJ_TYPE as "ο箢型", -- ο箢型 SESS_INFO.LOGON_TIME as "登rg", -- 登rg SESS_INFO.PROGRAM as "程式名Q", -- 程式名Q SESS_INFO.STATUS as "B", -- B SESS_INFO.LOCKWAIT as "等待i", -- 等待i SESS_INFO.ACTION as "幼", -- 幼 SESS_INFO.CLIENT_INFO as "客糍Y" -- 客糍Y 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系y的SPID
相关推荐
oraclemch 2020-11-06
yaluo00 2014-11-25
oraclestudyroad 2013-11-26
amberoracle 2012-02-07
teamojiao 2011-10-06
LinuxOracleWf 2010-12-18
ltoper 2019-04-04
沉默的羔羊 2019-04-03