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

转自：http://yajie.iteye.com/blog/1188309