with
lock_wait as(
SELECT l.addr,o.name as 阻塞对象, l.LMODE, l.trx_id, l.tid
FROM v$lock l, sysobjects o
WHERE l.table_id = o.id AND blocked = 1),
waiting as (
select a.addr,a.阻塞对象,a.LMODE,b.user_name,b.clnt_host,b.clnt_ip,b.sess_id,'被阻塞的事务: '||b.sql_text,b.trx_id,b.state
from lock_wait a inner join v$sessions b on a.trx_id = b.trx_id),
waiting_for as (
select a.addr,a.阻塞对象,a.LMODE,b.user_name,b.clnt_host,b.clnt_ip,b.sess_id,'待提交的事务: '||b.sql_text,b.trx_id,b.state
from lock_wait a inner join v$sessions b on a.tid = b.trx_id)
select * from waiting_for union all
select * from waiting;