/*
*  Author   : Vishal Gupta
*  Purpose  : Displays currently locked objects in database
*  Parameters : 1 - owner       (% - wildchar, \ - escape char) , this could also be passed as OWNER.OBJECT_NAME
*               2 - Object name (% - wildchar, \ - escape char)
*               3 - Object Type (% - wildchar, \ - escape char)
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  29-Jan-16  Vishal Gupta  Switched from gv$locked_objects to gv$lock, as 
*                           earlier view was giving information about locks on 
*                           temporary tables (with preserve on commit rows setting).
*                           - Also added machine and process in the output
*  06-Sep-12  Vishal Gupta  Added where clause to add more filtering
*  09-Aug-12  Vishal Gupta  Created
*
*/


/************************************
*  INPUT PARAMETERS
************************************/
UNDEFINE owner
UNDEFINE object_name
UNDEFINE object_type
UNDEFINE WHERE_CLAUSE

DEFINE owner="&&1"
DEFINE object_name="&&2"
DEFINE object_type="&&3"
DEFINE WHERE_CLAUSE="&&4"


COLUMN  _owner           NEW_VALUE owner            NOPRINT
COLUMN  _object_name     NEW_VALUE object_name      NOPRINT
COLUMN  _object_type     NEW_VALUE object_type      NOPRINT

set term off

SELECT DECODE(UPPER('&&owner'),'','%','&&owner')                   "_owner"
     , DECODE(UPPER('&&object_name'),'','%','&&object_name')       "_object_name"
     , DECODE(UPPER('&&object_type'),'','%','&&object_type')       "_object_type"
FROM DUAL;

SELECT CASE 
           WHEN INSTR('&&owner','.') != 0 THEN SUBSTR(UPPER('&&owner'),1,INSTR('&&owner','.')-1) 
           ELSE DECODE(UPPER('&&owner'),'','%',UPPER('&&owner')) 
       END                                                          "_owner"
     , CASE 
           WHEN INSTR('&&owner','.') != 0 THEN SUBSTR(UPPER('&&owner'),INSTR('&&owner','.')+1) 
           ELSE DECODE(UPPER('&&object_name'),'','%',UPPER('&&object_name')) 
       END    "_object_name"
FROM DUAL;
set term on



PROMPT **************************************************************
PROMPT * L O C K E D    O B J E C T S
PROMPT * 
PROMPT * Input Parameters 
PROMPT *  - Owner       = '&&owner'
PROMPT *  - Object Name = '&&object_name'
PROMPT *  - Object Type = '&&object_type'
PROMPT *  - Where Clause  = "&&WHERE_CLAUSE"
PROMPT **************************************************************

COLUMN object_name  HEADING "ObjectName"   FORMAT a40
COLUMN object_type  HEADING "ObjectType"   FORMAT a10 TRUNCATED
COLUMN locked_mode  HEADING "LockedMode"   FORMAT a10 
COLUMN sid          HEADING "SID"          FORMAT 99999 
COLUMN inst_id      HEADING "I#"           FORMAT 99 
COLUMN spid         HEADING "SPID"         FORMAT a6
COLUMN status       HEADING "Status"       FORMAT a8
COLUMN state        HEADING "State"        FORMAT a12 TRUNCATED
COLUMN logon_time   HEADING "Logon Time"   FORMAT a18
COLUMN username     HEADING "UserName"     FORMAT a20
COLUMN osuser       HEADING "OS User"      FORMAT a15 TRUNCATED
COLUMN MACHINE      HEADING "Machine"      FORMAT a20 TRUNCATED
COLUMN process      HEADING "Process"      FORMAT a11 
COLUMN program      HEADING "Program"      FORMAT a18 TRUNCATED
COLUMN event        HEADING "Event"        FORMAT a30 TRUNCATED
COLUMN last_call_et     HEADING "LastCall|(sec)" FORMAT a12
COLUMN sql_child_number HEADING "SQL|Child|No"   FORMAT 99

SELECT o.owner || '.' || o.object_name object_name
     , o.object_type
     , DECODE(l.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(l.lmode)) locked_mode
     , s.sid sid
     , s.inst_id
     , s.status
     , s.username
     , s.machine     
     , s.osuser
     , s.program
     , s.process
     , s.event
  FROM gv$lock l
       JOIN dba_objects o ON o.object_id = l.id1
       JOIN gv$session s ON s.inst_id = l.inst_id AND s.sid = l.sid
WHERE o.owner       like '&&owner'        ESCAPE '\'
  AND o.object_name like '&&object_name'  ESCAPE '\'
  AND o.object_type like '&&object_type'  ESCAPE '\'
  &&WHERE_CLAUSE
ORDER BY object_name       
;


UNDEFINE owner
UNDEFINE object_name
UNDEFINE object_type
UNDEFINE WHERE_CLAUSE