blocking

select r.type ||' LOCK:' ||' Session ' || b.sid ||' with Serial# '||b.serial#|| ' ,module=>'|| nvl(b.module,'Null') || ' and osuser=>' || nvl(b.osuser,'Null') || ' is Blocking other Sessions holding '|| r.type || ' Lock' , waittm
from
(select count(*) waitcnt,max(round(ctime/60)) waittm, a.type,a.id1
from gv$lock a, gv$session b
where request > 0 and a.sid = b.sid group by a.type,a.id1) r,
(select t.sid,t.id1,t.type,u.serial#,u.osuser,u.module
from gv$lock t, gv$session u
where t.sid = u.sid and t.block > 0) b
where r.id1 = b.id1 and r.type = b.type and
(select database_role
from v$database) = 'PRIMARY' order by r.id1,r.type;