Please see my other blog for Oracle EBusiness Suite Posts - EBMentors

Search This Blog

Note: All the posts are based on practical approach avoiding lengthy theory. All have been tested on some development servers. Please don’t test any post on production servers until you are sure.

Tuesday, March 15, 2011

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;

No comments: