oracle故障时，分析参考点

oracle 中查询占用session最多的进程

在故障发生时，尝试用下面的语句抓取数据库引起故障的点。

/*********************************************************************************************/
在oracle中监控死锁
/*********************************************************************************************/
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;    

/*********************************************************************************************/
定位引起oracle死锁的sql
/*********************************************************************************************/

select sql_text from v$sql where hash_value in 
(select sql_hash_value from v$session where sid in 
(select session_id from v$locked_object)) 

/*********************************************************************************************/
下面的SQL查询可以用于确定锁住数据库对象的锁：
/*********************************************************************************************/
select 
  c.owner, 
  c.object_name, 
  c.object_type, 
  b.sid, 
  b.serial#, 
  b.status, 
  b.osuser, 
  b.machine 
  from 
  v$locked_object a , 
  v$session b, 
  dba_objects c 
  where 
  b.sid = a.session_id 
  and 
  a.object_id = c.object_id; 

/*********************************************************************************************/
显示哪些会话被锁住
/*********************************************************************************************/
/* showlock.sql */
COLUMN o_name format a10
COLUMN lock_type format a20
COLUMN object_name format a15
SELECT RPAD (oracle_username, 10) o_name, session_id SID,
        DECODE (locked_mode,
                0, 'None',
                1, 'Null',
                2, 'Row share',
                3, 'Row Execlusive',
                4, 'Share',
                5, 'Share Row Exclusive',
                6, 'Exclusive'
               ) lock_type,
        object_name, xidusn, xidslot, xidsqn
   FROM v$locked_object, all_objects
WHERE v$locked_object.object_id = all_objects.object_id;

/*********************************************************************************************/
显示所有的TM和TX锁
/*********************************************************************************************/
/* showalllock.sql */
SELECT SID, TYPE, id1, id2,
        DECODE (lmode,
                0, 'None',
                1, 'Null',
                2, 'Row share',
                3, 'Row Exclusive',
                4, 'Share',
                5, 'Share Row Exclusive',
                6, 'Exclusive'
               ) lock_type,
        request, ctime, BLOCK
   FROM v$lock
WHERE TYPE IN ('TX', 'TM');

/*********************************************************************************************/
 在Oracle数据库中,可以通过kill session的方式来终止一个进程,其基本语法结构为:
 被kill掉的session,状态会被标记为killed,Oracle会在该用户下一次touch时清除该进程.
我们发现当一个session被kill掉以后，该session的paddr被修改，如果有多个session被kill，那么多个session
的paddr都被更改为相同的进程地址:
/*********************************************************************************************/
alter system kill session 'sid,serial#' ; 

/*********************************************************************************************/ 
在oracle中kill掉的进程有时还需要等待pmon回滚数据库已经占有的资源
有时候我们需要使用下面的脚本找出那些已经在oracle中kill掉的进程，在操作系统中在kill一次
/*********************************************************************************************/

select p.addr from v$process p where pid <> 1  
 minus  
select s.paddr from v$session s;

$ kill -9 &paddr

参考文献：

1)百度知道： http://zhidao.baidu.com/question/220430723.html