ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle 锁问题的解决

oracle 锁问题的解决

原创 Linux操作系统 作者:zhengbao_jun 时间:2011-08-25 14:08:04 0 删除 编辑

可以用Spotlight软件对数据库的运行状态进行监控。

当出现session锁时,我们要及时进行处理.

1. 查看哪些session锁:
SQL语句:select 'alter system kill session '''||sid||','||serial#||''';' from v$session where sid in (select sid from v$lock where block = 1);

SQL> select 'alter system kill session '''||sid||','||serial#||''';' from v$session where sid in (select sid from v$lock where block = 1);
'ALTERSYSTEMKILLSESSION'''||SID||','||SERIAL#||''';'
--------------------------------------------------------------------------------
alter system kill session '132,731';
alter system kill session '275,15205';
alter system kill session '308,206';
alter system kill session '407,3510';

2. 查看session锁.
sql语句:select s.sid, q.sql_text from v$sqltext q, v$session s
where q.address = s.sql_address
and s.sid = &sid
order by piece;

SQL> select s.sid,q.sql_text from v$sqltext q, v$session s where q.address = s.sql_address and s.sid in (select sid from v$lock where block = 1) order by piece;
        SID SQL_TEXT
---------- ----------------------------------------------------------------
       77 UPDATE PROFILE_USER SET ID=1,COMPANY_ID=2,CUSTOMER_ID=3,NAMED  
       77 _INSURED_ID=4,LOGIN=5,ROLE_ID=6,PASSWORD=7,EMAIL=8,TIME_ZON
       77 E=9 WHERE PROFILE_USER.ID=:34
3 rows selected.


3. kill锁的进程.
SQL语句:alter system kill session '77,22198';

SQL> alter system kill session '391,48398';
System altered.

4. 查看谁锁了谁。
select s1.username || [email='@']'@'[/email] || s1.machine
  || ' ( SID=' || s1.sid || ' )  is blocking '
  || s2.username || [email='@']'@'[/email] || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
  from v$lock l1, v$session s1, v$lock l2, v$session s2
  where s1.sid=l1.sid and s2.sid=l2.sid
  and l1.BLOCK=1 and l2.request > 0
  and l1.id1 = l2.id1
  and l2.id2 = l2.id2 ;

注:
> : 重定向输出,将文件的标准输出重新定向输出到文件,或将数据文件作为另一程序的标准输入内容。
| UNIX管道:将一文件的输出作为另一文件的输入.

在执行SQL语句试:alter system kill session '391,48398'(sid为391); 应当注意对于sid在100以下的应当谨慎,可能该进程对应某个application,如对应某个事务,可以kill.

 

 

------------------------------------------------------csdn 网友的SQL-------------------------------

 

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;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/235507/viewspace-705778/,如需转载,请注明出处,否则将追究法律责任。

上一篇: oracle sid
请登录后发表评论 登录
全部评论

注册时间:2008-08-08

  • 博文量
    209
  • 访问量
    879680