--第一种方式：
--查询当前的活动会话，其中lockwait列不为空，表示会话正在等待一把锁
select * from v$session where v$session."STATUS" = 'ACTIVE' and v$session."USERNAME" = 'SHANGHAI';
--lockwait对应v$lock的kaddr列，表示锁的地址
select * from v$lock where v$lock."KADDR" = '000007FFDA22EC18';
--查询该锁的锁定记录，其中request为0表示是制造锁定的会话插入的记录
select * from v$lock where v$lock."ID1" in ('327688') and request = 0;
--根据v$lock的sid查询制造锁的会话
select v$session."sql_ID",v$session.* from v$session where v$session."SID" = '2279';
--查询该会话正在执行或者执行过的sql
select * from v$sqltext where v$sqltext."sql_ID" = '5f282d2hp03k5' order by v$sqltext."PIECE" asc;


--第二种方式：
select v$session."SID",v$session."BLOCKING_SESSION",v$session."BLOCKING_SESSION_STATUS",v$session."STATUS"
 from v$session
where v$session."USERNAME" = 'SHANGHAI';