Oracle : Script shows all latches held in the database

select
substrb(vs.username,1,10) db_user,
substrb(vs.osuser,1,10) os_user,
substrb(vs.terminal,1,8) terminal,
decode(vs.lockwait,null,substrb(vs.status,1,3),'WAI') "STA",
substrb(vs.server,1,1)||substrb(to_char(vs.sid),1,4) sid,
substrb(to_char(vs.serial#),1,4) srl,
substrb(vp.spid,1,8) spid,
substrb(vl.name,1,21) latch
from v$session vs, v$process vp, v$latchholder vl
where vs.sid = vl.sid
and vs.type = 'USER'
and vs.paddr = vp.addr(+)
/

Script to look at session wait to see who is waiting on latches
=======================================================================

select /*+ RULE CURSOR_SHARING_EXACT */
  substrb(vs.username,1,12) db_user,
  decode(vs.lockwait,null,
    decode(vp.latchwait,null,
      decode(vp.latchspin, null,
        substrb(vs.status,1,3),
        'SPN'),
      'LAT'),
    'LCK') STA,
  substrb(to_char(vs.sid),1,4) sid,
  substrb(vp.spid,1,8) spid,
  vsw.p1raw LATCH_ADDR,
  vs.sql_address SQL_ADDR,
  vs.sql_hash_value SQL_HASH_VALUE,
  to_char(vsw.seconds_in_wait,'9999') TIME,
  substrb(vlc.name,1,30) LATCH_NAME,
  substrb(vlc.child#,1,5) CHILD,
  to_char(vlc.gets,'99999999') "GETS",
--  to_char(vlc.misses,'999999') "MISSES",
  to_char(vlc.sleeps,'999999') "SLEEPS"
from v$session_wait vsw, v$latch_children vlc, v$session vs, v$process vp
where vsw.p1raw = vlc.addr
and vsw.sid = vs.sid
and vs.paddr = vp.addr
and vsw.event like 'latch%'
union
select /*+ RULE */
  substrb(vs.username,1,12),
  decode(vs.lockwait,null,
    decode(vp.latchwait,null,
      decode(vp.latchspin, null,
        substrb(vs.status,1,3),
        'SPN'),
      'LAT'),
    'LCK') STA,
  substrb(to_char(vs.sid),1,4),
  substrb(vp.spid,1,8),
  vsw.p1raw,
  vs.sql_address,
  vs.sql_hash_value SQL_HASH_VALUE,
  to_char(vsw.seconds_in_wait,'9999'),
  substrb(vl.name,1,30),
  'N/A',
  to_char(vl.gets,'99999999'),
--  to_char(vl.misses,'999999'),
  to_char(vl.sleeps,'999999')
from v$session_wait vsw, v$latch vl, v$session vs, v$process vp
where vsw.p1raw = vl.addr
and vsw.sid = vs.sid
and vs.paddr = vp.addr
and vsw.event like 'latch%'
order by 5,6
/