想飞就别怕摔

大爷的并TM骂人

Oracle强杀进程,解决表锁死等问题

 1、找到sid,serial#;

SELECT /*+ rule */ s.username, l.type,
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,s.status 
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 order by l.type;

2、根据找到的sid,serial#,执行以下语句,清除进程。
alter system kill session '~sid~,~serial#~';
例如: alter system kill session '14,4820';

如果死锁不能自动释放,就需要我们手工的kill session。 步骤如下:
 
1.       查看有无死锁对象,如有kill session
 
SELECT   'alter system kill session ''' || sid || ',' || serial# || ''';'  "Deadlock"
  FROM   v$session
 WHERE   sid IN (SELECT   sid
                   FROM   v$lock
                  WHERE   block = 1);
如果有,会返回类似与如下的信息:
alter system kill session '132,731';
alter system kill session '275,15205';
alter system kill session '308,206';
alter system kill session '407,3510';
 
kill session:
执行alter system kill session '391,48398'(sid为391);
注意:应当注意对于sid在100以下的应当谨慎,可能该进程对应某个application,如对应某个事务,可以kill.
 
 
2.       查看导致死锁的SQL
 
  SELECT   s.sid, q.sql_text
    FROM   v$sqltext q, v$session s
   WHERE   q.address = s.sql_address AND s.sid = &sid  -- 这个&sid 是第一步查询出来的
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. 查看谁锁了谁
SELECT      s1.username
         || '@'
         || s1.machine
         || ' ( SID='
         || s1.sid
         || ' )  is blocking '
         || s2.username
         || '@'
         || 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;
 
或者
 
  SELECT                                                          
        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
 
 
 
三.锁 和 阻塞
 
3.1 相关概念
 
         通常来讲,系统如果平时运行正常,突然会停止不动,多半是被阻塞(Blocked)住了。 我们可以通过v$lock 这张视图,看查看阻塞的信息。
 
SQL> desc v$lock;
 名称                      是否为空? 类型
 ----------------------------------------- -------- -----------------
 ADDR                     RAW(4)
 KADDR                    RAW(4)
 SID                       NUMBER
 TYPE                      VARCHAR2(2)
 ID1                       NUMBER
 ID2                       NUMBER
 LMODE                    NUMBER
 REQUEST                   NUMBER
 CTIME                     NUMBER
 BLOCK                     NUMBER
 
 
   
 
我们关注的比较多的是request 和 block 字段。
如果某个request列是一个非0值,那么它就是在等待一个锁。  如果block列是1,这个SID 就持有了一个锁,并且阻塞别人获得这个锁。 这个锁的类型由TYPE 字段定义。锁的模式有LMODE 字段定义,ID1和ID2 字段定义了这个锁的相关信息。ID1相同,就代表指向同一个资源。这样就有可能有加锁者和等待者。  LMODE 的6中模式参考上面的TM锁类型表。
 
可以结合v$lock 和 v$session 视图来查询相关的信息:
 
        
  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;
 
或者
 
SELECT                                                            
      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

posted on 2011-10-09 16:22 生命的绽放 阅读(814) 评论(0)  编辑  收藏 所属分类: 数据库


只有注册用户登录后才能发表评论。


网站导航:
 
<2011年10月>
2526272829301
2345678
9101112131415
16171819202122
23242526272829
303112345

导航

统计

常用链接

留言簿(5)

随笔分类(94)

随笔档案(93)

文章分类(5)

文章档案(5)

相册

JAVA之桥

SQL之音

兄弟之窗

常用工具下载

积分与排名

最新评论

阅读排行榜