Blocking Waiting Sessions

Create Or Replace Force View Sys.Av_blocking_waiting_sess (Waiting_session,
                                                           Holding_session,
                                                           Lock_or_pin,
                                                           Address,
                                                           Mode_held,
                                                           Mode_requested
                                                          )

As
   Select /*+ ordered */
          W1.Sid Waiting_session, H1.Sid Holding_session,
          W.Kgllktype Lock_or_pin, W.Kgllkhdl Address,
          Decode (H.Kgllkmod,
                  0, 'None',
                  1, 'Null',
                  2, 'Share',
                  3, 'Exclusive',
                  'Unknown'
                 ) Mode_held,
          Decode (W.Kgllkreq,
                  0, 'None',
                  1, 'Null',
                  2, 'Share',
                  3, 'Exclusive',
                  'Unknown'
                 ) Mode_requested
     From Dba_kgllock W, Dba_kgllock H, V$session W1, V$session H1
    Where (    (    (H.Kgllkmod != 0)
                And (H.Kgllkmod != 1)
                And (   (H.Kgllkreq = 0)
                     Or (H.Kgllkreq = 1))
               )
           And (    (   (W.Kgllkmod = 0)
                     Or (W.Kgllkmod = 1))
                And (    (W.Kgllkreq != 0)
                     And (W.Kgllkreq != 1))
               )
          )
      And W.Kgllktype = H.Kgllktype
      And W.Kgllkhdl = H.Kgllkhdl
      And W.Kgllkuse = W1.Saddr
      And H.Kgllkuse = H1.Saddr;