查询阻塞与被阻塞SQL语句，供参考：
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
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
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/10271187/viewspace-1658097/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
最新文章
- 12.2新特性之ADG多节点启动MRP进程
- ORA-01511 error in renaming log/data files ORA-00261 log 4 of thread 1 is being
- Warning: ORA-16714: the value of property ArchiveLagTarget is inconsistent
- 恢复数据库hang住/parallel recovery read buffer free
- ORA-00600: 内部错误代码，参数: [6002], [6], [28], [1], [52], [], [], []
- ORA-13639: The CURRENT operation was interrupted because it timed OUT
- 表空间小写导致ORA-31655错误
- ORA-12154:TNS:无法解析指定的连接标识符
- 测试将表缓存到keep池
- ASM file metadata operation
