oracle查询阻塞与被阻塞SQL语句

# 环境:

操作系统

$ uname -a

Linux jinhu-db02 2.6.32-696.el6.x86_64 #1 SMP Tue Mar 21 19:29:05 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux

$ cat /etc/redhat-release 

CentOS release 6.9 (Final)

数据库

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE 11.2.0.4.0 Production

TNS for Linux: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 - Production

select b.sid,

       a.sql_id,

       a.sql_text,

       a.hash_value,

       b.username,

       b.machine,

       a.module,

       decode(c.block, 1, 'blocking') blocking,

       decode(c.request, 0, 'null', 'blocked') blocked,

       to_char(b.logon_time, 'yyyy-mm-dd hh24:mi:ss')

  from v$sql a, v$session b, v$lock c

 where c.type = 'TX'

   and a.sql_id = b.sql_id

   and b.sid = c.sid

union all

select b.sid,

       a.sql_id,

       a.sql_text,

       a.hash_value,

       b.username,

       b.machine,

       a.module,

       decode(c.block, 1, 'blocking') blocking,

       decode(c.request, 0, 'null', 'blocked') blocked,

       to_char(b.logon_time, 'yyyy-mm-dd hh24:mi:ss')

  from v$sql a, v$session b, v$lock c

 where c.type = 'TX'

   and a.sql_id = b.prev_sql_id

   and b.sid = c.sid

   and c.block = 1

   

   

!!The End!!