1. 查询是否有阻塞

select s.USER_NAME,s.sess_id,s.TRX_ID,s.SQL_TEXT,s.RUN_STATUS from v$sessions s,v$lock l where s.trx_id=l.trx_id and l.blocked=1;

2. 查阻塞源头

select  * from v$sessions where trx_id in (select  wait_for_id from v$trxwait where wait_for_id not in  (select id from v$trxwait))

3. 查询被阻塞的信息和引起阻塞的信息

SELECT SYSDATE STATTIME,DATEDIFF(SS,S1.LAST_SEND_TIME,SYSDATE) SS,
        '被阻塞的信息'   WT,S1.SESS_ID WT_SESS_ID,S1.SQL_TEXT WT_SQL_TEXT,S1.STATE WT_STATE,S1.TRX_ID WT_TRX_ID,
        S1.USER_NAME WT_USER_NAME,S1.CLNT_IP WT_CLNT_IP,S1.APPNAME WT_APPNAME,S1.LAST_SEND_TIME WT_LAST_SEND_TIME,
        '引起阻塞的信息' FM,S2.SESS_ID FM_SESS_ID,S2.SQL_TEXT FM_SQL_TEXT,S2.STATE FM_STATE,S2.TRX_ID FM_TRX_ID,
        S2.USER_NAME FM_USER_NAME,S2.CLNT_IP FM_CLNT_IP,S2.APPNAME FM_APPNAME,S2.LAST_SEND_TIME FM_LAST_SEND_TIME
 FROM V$SESSIONS S1,V$SESSIONS S2,V$TRXWAIT W
 WHERE S1.TRX_ID=W.ID
   AND S2.TRX_ID=W.WAIT_FOR_ID;

4. 查询阻塞关系

with sel_lock as
 (SELECT O.NAME, O.schid, o.pid, L.*
    FROM V$LOCK L, SYSOBJECTS O
   WHERE L.TABLE_ID = O.ID
     AND BLOCKED = 1),
locks as
 (select b.sess_id, b.sql_text, a.name, a.addr, a.schid, b.trx_id
    from sel_lock a, V$sessions b
   where a.trx_id = b.trx_id),
locks_wait as
 (select b.sess_id, b.sql_text, a.addr, b.trx_id
    from sel_lock a, V$sessions b
   where a.tid = b.trx_id)
select a.sess_id 被阻塞会话,
       a.sql_text 被阻塞语句,
       b.sess_id 造成阻塞会话,
       b.sql_text 造成阻塞语句,
       a.name,
       (select name
          from sysobjects
         where ID = a.schid
           and type$ = 'NVWATSTYBZ') 模式名
  from locks a, locks_wait b
 where a.addr = b.addr;

5. 查看数据库的连接信息

SELECT 'sp_close_session('||A.SESS_ID||');',
A.SESS_ID AS 会话id,
A.SQL_TEXT AS SQL语句,
A.STATE AS 会话状态,
A.N_USED_STMT AS 当前会话使用句柄数量,
A.CURR_SCH AS 当前模式,
A.USER_NAME AS 用户名,
A.TRX_ID AS 事务ID,
A.CREATE_TIME AS 会话创建时间,
A.CLNT_TYPE AS 客户端类型,
A.TIME_ZONE AS 时区,
A.OSNAME AS 操作系统名称,
A.CONN_TYPE AS 连接类型,
B.PROTOCOL_TYPE AS 协议类型,
B.IP_ADDR AS 访问ip地址
FROM SYS.V$SESSIONS A ,SYS.V$CONNECT B where A.Sess_id= B.SADDR AND A.USER_NAME = 'NV2' ORDER BY 
SF_GET_EP_SEQNO(A.rowid),A.Sess_id ;

6. 杀会话

sp_close_session('xxx')