恋恋风尘

oracle解锁

2010年3月6日 | 标签:

曾经碰到一次用户在更新一个数据的时候死机的情况，后来查找到是某位同事写的一条update语句没有写commit,真应该拉出去打屁股。解决的方法如下
招数一
查看被锁的表：

select p.spid,c.object_name,b.session_id,b.oracle_username,b.os_user_name from v$process p,v$session a, v$locked_object b,all_objects c where p.addr=a.paddr and a.process=b.process and c.object_id=b.object_id

解锁

alter system kill session '146'//(其中146为锁住的进程号)

招数二

SELECT sn.username, m.SID,sn.SERIAL#, m.TYPE,
   DECODE (m.lmode,
   0, 'None',
   1, 'Null',
   2, 'Row Share',
   3, 'Row Excl.',
   4, 'Share',
   5, 'S/Row Excl.',
   6, 'Exclusive',
   lmode, LTRIM (TO_CHAR (lmode, '990'))
   ) lmode,
   DECODE (m.request,
   0, 'None',
   1, 'Null',
   2, 'Row Share',
   3, 'Row Excl.',
   4, 'Share',
   5, 'S/Row Excl.',
   6, 'Exclusive',
   request, LTRIM (TO_CHAR (m.request, '990'))
   ) request,
   m.id1, m.id2
   FROM v$session sn, v$lock m
   WHERE (sn.SID = m.SID AND m.request != 0) --存在锁请求，即被阻塞
   OR ( sn.SID = m.SID --不存在锁请求，但是锁定的对象被其他会话请求锁定
   AND m.request = 0
   AND lmode != 4
   AND (id1, id2) IN (
   SELECT s.id1, s.id2
   FROM v$lock s
   WHERE request != 0 AND s.id1 = m.id1
   AND s.id2 = m.id2)
   )
ORDER BY id1, id2, m.request;

alter system kill session '91';
alter system kill session '144,633';
alter system kill session '91,21';
alter system kill session '112,5772';

再列几种查看锁的方法
招数三

--查看锁
select a.object_name||'|'||b.session_id||'|'||c.serial#||'|'||c.program||'|'||c.username||'|'||c.command||'|'||c.machine||'|'||
c.lockwait from all_objects a,v$locked_object b,v$session c where a.object_id=b.object_id and c.sid=b.session_id;

--解锁
alter system kill session'session_id,serial#';
--列句
alter system kill session'453,10316';

招数四

招数五

--查看锁
select t2.username,t2.sid,t2.serial#,t2.logon_time
from v$locked_object t1,v$session t2
where t1.session_id=t2.sid order by t2.logon_time;

--alter system kill session 'sid,serial#';
--把锁给KILL掉
alter system kill session '146,21177';

招数六

--用语句得到详细的被锁住的表的信息，语句如下：
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
--可以得到Sid和Serial#，接着执行以下语句就可以解锁：

alter system kill session 'sid,serial#';

发表评论 | Trackback