with
lock_wait as(
SELECT l.addr,o.name as 阻塞对象, l.LMODE, l.trx_id, l.tid
FROM vlock 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 vsessions 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;