@@header

/*

Author   : Vishal Gupta
Purpose  : Displays currently locked objects in database

*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  05-Aug-04  Vishal Gupta  First Draft
*  25-Sep-09  Vishal Gupta  Modified to use v$lock as DBA_LOCK might not 
*                           exist in some databases if rdbms/admin/dbablock.sql  
*                           has not been run.
*/

COLUMN Blocking        HEADING "Bl'ing"                 FORMAT a6
COLUMN Blocked         HEADING "Bl'ed"                  FORMAT a6 
COLUMN Since                                            FORMAT a18
COLUMN inst_id         HEADING 'I#'                     FORMAT 99
rem COLUMN sid                                          FORMAT 9999
COLUMN username                                         FORMAT a15
COLUMN osuser                                           FORMAT a11 TRUNC
COLUMN machine                                          FORMAT a15 
COLUMN lock_type                                        FORMAT a22 TRUNC
COLUMN mode_held                                        FORMAT a13
COLUMN mode_requested                                   FORMAT a13
COLUMN mode_requested                                   FORMAT a13
COLUMN owner                                            FORMAT a15
COLUMN object_type                                      FORMAT a10
COLUMN object_name                                      FORMAT a25


SELECT /*+ RULE */ 
       DECODE(lk.block,1,'Y',0,' ', lk.block) Blocking
     , DECODE(s.lockwait,NULL,' ','Y') Blocked
--   ,    DECODE(block,
--           0, 'Not Blocking',  /* Not blocking any other processes */
--        1, 'Blocking',      /* This lock blocks other processes */
--        2, 'Global',        /* This lock is global, so we can't tell */
--        TO_CHAR(block)) blocking_others
     , TO_CHAR(sysdate - (lk.ctime/24/60/60),'DD-MON-YY hh24:MI:SS') Since
     , lk.inst_id
     , lk.sid
     , s.status
     , s.username
     , s.osuser
     , NVL(t.name, lk.type) lock_type
     , DECODE(lk.lmode,
              0, 'None',           /* Mon Lock equivalent */
              1, 'Null',           /* N */
              2, 'Row-S (SS)',     /* L */
              3, 'Row-X (SX)',     /* R */
              4, 'Share',          /* S */
              5, 'S/Row-X (SSX)',  /* C */
              6, 'Exclusive',      /* X */
              TO_CHAR(lmode)) mode_held
     , DECODE(request,
    0, 'None',           /* Mon Lock equivalent */
    1, 'Null',           /* N */
    2, 'Row-S (SS)',     /* L */
    3, 'Row-X (SX)',     /* R */
    4, 'Share',          /* S */
    5, 'S/Row-X (SSX)',  /* C */
    6, 'Exclusive',      /* X */
    TO_CHAR(request)) mode_requested
     , NVL(ob.object_type,' ') object_type
     , NVL(ob.owner,' ') owner
     , NVL(ob.object_name,' ') object_name 
  FROM gv$lock  lk
     , dba_objects ob
     , gv$session s
     , v$lock_type t
 WHERE lk.inst_id = s.inst_id
   AND lk.sid = s.sid
   AND lk.id1 = ob.object_id (+)
   AND lk.type = t.type (+)
   AND lk.type NOT IN 
       (
      'MR' -- Media Recovery
    , 'AE' -- Edition Lock
    )
--   AND (lk.block = 1 OR s.lockwait IS NOT NULL )
ORDER BY lk.block desc, s.lockwait desc , s.sid, ob.object_name 
/

@@footer