如果发生了锁等待，我们可能更想知道是谁锁了表而引起谁的等待
以下的语句可以查询到谁锁了表:

复制代码 代码如下:
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

声明：本站（华域联盟www.cnhackhy.com）所有文章，如无特殊说明或标注，均为本站原创发布。任何个人或组织，在未征得本站同意时，禁止复制、盗用、采集、发布本站内容到任何网站、书籍等各类媒体平台。如若本站内容侵犯了原著者的合法权益，可联系我们进行处理。