@@header

/*

Author   : Vishal Gupta
Purpose  : Displays blocking locks in database

*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  --------------------------------------------------------
*  19-Oct-12  Vishal Gupta  Added ClientProcess in output and changed column sizing
*  18-Apr-12  Vishal Gupta  Replace with gv$session with materialize hinted
*                           sub-query factoring (CTE - common table extression)
*  27-Feb-12  Vishal Gupta  Created
*
*/

PROMPT 
PROMPT *********************************
PROMPT * B L O C K I N G    L O C K S 
PROMPT *********************************
PROMPT 

COLUMN INST_ID                        HEADING "I#"                          FORMAT 99
COLUMN SID                                                                  FORMAT a40
COLUMN username                       HEADING "UserName"                    FORMAT a20 TRUNC
COLUMN lock_type                      HEADING "Lock|type"                   FORMAT a4  TRUNC
COLUMN lock_Name                      HEADING "Lock Name"                   FORMAT a22 TRUNC 
COLUMN osuser                         HEADING "OSUser"                      FORMAT a20 TRUNC
COLUMN machine                        HEADING "Client|Machine"              FORMAT a20 TRUNC 
COLUMN process                        HEADING "Client|Process"              FORMAT a10
COLUMN program                        HEADING "Program"                     FORMAT a30 TRUNC
COLUMN event                          HEADING "Event"                       FORMAT a30 TRUNC


--
----
---- Need to use Subquery for gv$session, otherwise session is disconnected with 
---- ORA-07445 [qksopCheckPropOpt] when joining dynamic performance views in heirarichal query.
----
--WITH  sess as 
--  ( select  /*+ RULE  NO_MERGE */ s1.* from gv$session s1
--  )
--, locks as
--   (select  /*+ RULE NO_MERGE */ * 
--     from gv$lock where lmode = 0 
--   --AND ( lmode = 0 OR  (lmode > 0 AND block > 0))
-- )
--SELECT /*+ RULE */ 
--       s.inst_id
--     , DECODE(level
--           ,1 , s.sid || ' (Blocker)'    
--     , ' ' || LPAD('-',(level-1)*4,'|---') || '> ' || s.sid 
--     ) 
--    sid
--     , s.status
--     , lt.name lock_name
--     , s.username
--     , s.osuser
--     , s.sql_id
--     , s.process
--     , s.program
--     , s.event
--FROM  sess s
--      LEFT OUTER JOIN locks l ON  l.inst_id = s.inst_id AND l.sid     = s.sid 
--      LEFT OUTER JOIN v$lock_type lt ON l.type  = lt.type 
--WHERE (  s.blocking_session IS NOT NULL
--         OR exists (select 1 from sess s2 where s2.blocking_instance = s.inst_id and s2.blocking_session = s.sid)
--       )
--CONNECT BY PRIOR s.inst_id = s.blocking_instance
--       AND PRIOR s.sid     = s.blocking_session
--START WITH s.blocking_session IS NULL
--;

SELECT l.inst_id, DECODE(l.request,0,'Holder ','---> Waiter ')|| l.sid sid,l.id1, l.id2, l.lmode, l.request, lt.name, l.CTIME, l.BLOCK
     , s.username
FROM gV$LOCK l
   , gv$session s
   , gv$lock_type lt
WHERE l.inst_id = s.inst_id 
  AND l.sid = s.sid
  AND l.addr = s.
  AND l.type = lt.type
  AND (l.id1, l.id2, l.type) IN (SELECT l2.id1, l2.id2, l2.type FROM gV$LOCK l2 WHERE l2.request>0)
ORDER BY l.id1, l.request;


SELECT s.inst_id
     , DECODE(level
           ,1 , s.sid || ' (Blocker)'    
     , ' ' || LPAD('-',(level-1)*4,'|---') || '> ' || s.sid 
     ) 
    , s.blocking_session
    , s.blocking_instance
    , s.final_blocking_session
    , s.final_blocking_instance
    , s.status
--    , lt.name lock_name
    , s.username
    , s.osuser
    , s.sql_id
    , s.process
    , s.program
    , s.event    
  FROM gv$session s
     , gv$lock l 
     , gv$lock_type lt
WHERE s.inst_id =  l.inst_id
  AND s.sid = l.sid
  AND l.type = lt.type
START WITH s.blocking_session is null  
CONNECT BY NOCYCLE
   PRIOR s.inst_id = s.blocking_instance
     AND s.sid     = s.blocking_session
;

@@footer