#!/bin/bash
# whatblock ()
#------------------------------------------------------------------------------#
#    Module Name:  whatblock                                                   #
#                                                                              #
#    Purpose:      Report on the Blocking Locks.                               #
#                                                                              #
#    Maintenance                                                               #
#    Date          Author        Description                                   #
#    -----------   ------------  --------------------------------------------  #
#    19-Jun-2026   M.Barone      Module design/creation                        #
#------------------------------------------------------------------------------#
{
        DIRNAM=$(dirname $0)
        ORASID=$(echo $CONTEXT_NAME | cut -f1 -d'_')
        HNAME=$(uname -n)
 
        clear
        echo
        echo  " #--------------------------------------------------------#"
        echo  " # whatblock:                  Oracle Apps Locked-Objects #"
        echo  " #--------------------------------------------------------#"
        echo
        echo  " Connecting as SYSDBA"
        sleep 2
 
        clear
        ORANAM=${ORASID}
 
#------------------------------------------------------------------------------#
#  SQL*Plus ("Here-Document")                                                  #
#------------------------------------------------------------------------------#
sqlplus  -s  /nolog  <<-END_FILE
 
        CONNECT / AS SYSDBA
 
        clear   BREAKS
        clear   BUFFER
        clear   COLUMNS
        clear   COMPUTES
        clear   SCREEN
        clear   SQL
        clear   TIMING
 
        set     feedback        on
        set     flush           on
        set     heading         on
        set     pause           off
        set     space           1
        set     termout         on
        set     verify          on
        set     linesize        125
        set     pagesize        100
 
        SPOOL /tmp/whatblock_$$.txt
 
        COLUMN  datevalue       NOPRINT NEW_VALUE       DATEVAR
        COLUMN  timevalue       NOPRINT NEW_VALUE       TIMEVAR
 
        COLUMN  linuxuser       FORMAT  A09           Heading 'LinuxUser'
        COLUMN  oracleuser      FORMAT  A10           Heading 'OracleUser'
        COLUMN  id1             FORMAT  99999999999   Heading 'Locked Object'
        COLUMN  id2             FORMAT  9999999       Heading 'Resource|Number'
        COLUMN  sid_serial      FORMAT  A10           Heading 'Session|Sid|Serial'
        COLUMN  ser             FORMAT  A07           Heading 'Session|Serial'
        COLUMN  aud             FORMAT  A07           Heading 'Session|AudSid'
        COLUMN  lresource       FORMAT  A26           Heading 'Resource|Name'  -
                                                      WORD_WRAPPED
        COLUMN  lprogram        FORMAT  A50           Heading 'Program'        -
                                                      WORD_WRAPPED
        COLUMN  lmode           FORMAT  A04           Heading 'Lock|Mode'
        COLUMN  lrequest        FORMAT  A04           Heading 'Rqst|Mode'
        COLUMN  ltype           FORMAT  A04           Heading 'Lock|Type'
 
        TTITLE  CENTER  'Oracle Blocking Locks By User Name'            -
                LEFT    DATEVAR                                         -
                RIGHT   'Page:'         FORMAT 999 SQL.pno      skip 1  -
                CENTER  'whatblock -- Sorted By Object and Locker-ID'   -
                LEFT    TIMEVAR                                 skip 1  -
                CENTER  'KD:KeyDeformationIndexActivity '               -
                        'TX:TransactionEnqueueExclusive(DML) '          -
                        'RT:RedoThread '                                -
                        'UL:UserDefinedLock'                    skip 1  -
                CENTER  'v\$Lock_Type Contains Definitions for V\$LOCK' skip 2
 
        SELECT
            TO_CHAR(sysdate, 'DD-MON-YY') AS datevalue,
            TO_CHAR(sysdate, 'HH24:MI:SS') AS timevalue,
            PRO.username AS linuxuser,
            SES.username AS oracleuser,
            LCK.type AS ltype,
            LCK.lmode || '(' || DECODE(LCK.lmode, 0,' ', 1,'N', 2,'L', 3,'S', 4,'R', 5,'C', 6,'X', ' ') || ')' AS lmode,
            LCK.request || '(' || DECODE(LCK.request, 0,' ', 1,'n', 2,'l', 3,'s', 4,'r', 5,'c', 6,'x', ' ') || ')' AS lrequest,
            SES.sid || ',' || SES.serial# AS sid_serial,
            -- Resolve Resource Name dynamically based on lock type
            CASE
                WHEN LCK.type = 'TM' THEN (SELECT OBJ.object_name || ' (' || OBJ.object_type || ')' FROM dba_objects OBJ WHERE OBJ.object_id = LCK.id1)
                WHEN LCK.type = 'TX' THEN 'TX - Undo Seg: ' || TRUNC(LCK.id1/65536) || ' Slot: ' || MOD(LCK.id1,65536) || ' Seq: ' || LCK.id2
                ELSE 'ID1: ' || LCK.id1 || ' | ID2: ' || LCK.id2
            END AS lresource,
            SES.program AS lprogram
        FROM
            V\$LOCK LCK
            LEFT JOIN V\$SESSION SES ON LCK.sid = SES.sid
            LEFT JOIN V\$PROCESS PRO ON SES.paddr = PRO.addr
        WHERE
            LCK.lmode = 6
            OR LCK.request <> 0
        ORDER BY
            LCK.id1,
            LCK.id2,
            SES.username,
            SES.sid;
 
        EXIT;
 
END_FILE
 
echo
echo "Please see /tmp/whatblock_$$.txt"
echo
}

