查询分析清理oracle阻塞会话

1.查询CMS用户当前系统活动的会话

select
t.SID,t.SERIAL#,t.STATUS,t.LOGON_TIME,t.LAST_CALL_ET,t.PROGRAM,t.LOCKWAIT,
t.BLOCKING_SESSION,t.BLOCKING_SESSION_STATUS,t.BLOCKING_INSTANCE,
t.USERNAME,t.PROCESS,t.OSUSER,t.SERVER,t.OSUSER,
t.MACHINE,t.TERMINAL,t.PROCESS,t.PROGRAM,t.type,t.MODULE_HASH
,t.ROW_WAIT_OBJ#,t.ROW_WAIT_FILE#,t.ROW_WAIT_BLOCK#,t.ROW_WAIT_ROW#,t.EVENT,t.STATE,t.SERVICE_NAME
from v$session t
where
t.USERNAME = 'CMS' and t.STATUS = 'ACTIVE';

2.查询CMS用户所有活动会话的sql语句

select sql_text, st.SQL_ID, st.COMMAND_TYPE
  from v$sqltext st, v$session s
 where (st.sql_id = s.sql_id)
   and s.sid in (select t.SID
                   from v$session t
                  where t.USERNAME = 'CMS'
                    and t.STATUS = 'ACTIVE');

3.查询当前系统的锁住会话及对象

SELECT b.owner,b.object_name,a.session_id,a.locked_mode 
    FROM v$locked_object a ,dba_objects b  
    WHERE b.object_id = a.object_id; 

4.杀死一个会话

alter system kill session 'sid, serial#';

5.如果要一次性杀死多个会话，一个一个填写sid和serial#十分的繁琐，应该在查询被锁对象的同时拼凑出多条的杀会话语句，以分号分隔，一起复制下来，然后就可以批量的执行了。

拼凑kill语句的方式如下，下面加了一个过滤条件和一个排序，杀除真正关心的表，并且着重注意超时时间过长的会话。

SELECT'alter system kill session '''|| c.sid ||''||','|| c.serial# ||''';',
        a.object_id, a.session_id, b.object_name, c.*
   FROM v$locked_object a, dba_objects b, v$session c
  WHERE a.object_id = b.object_id
   AND a.SESSION_ID = c.sid(+)
    AND schemaname ='Unmi'
  ORDER BY logon_time；