#!/usr/bin/ksh
# whatblock ()
#------------------------------------------------------------------------------#
#    Module Name:  whatblock                                                   #
#                                                                              #
#    Purpose:      Report on the Blocking Locks.                               #
#                                                                              #
#    Maintenance                                                               #
#    Date          Author        Description                                   #
#    -----------   ------------  --------------------------------------------  #
#    10-Oct-2009   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        off
        set     flush           on
        set     heading         on
        set     pause           off
        set     space           1
        set     termout         on
        set     verify          on
        set     linesize        80
        set     pagesize        999

        SPOOL /tmp/whatblock_$$.txt

        COLUMN  datevalue       NOPRINT NEW_VALUE       DATEVAR
        COLUMN  timevalue       NOPRINT NEW_VALUE       TIMEVAR

        COLUMN  usr             FORMAT  A08           Heading 'UserName'
        COLUMN  obj             FORMAT  A20           Heading 'Object|Name'
        COLUMN  sid             FORMAT  A07           Heading 'Session|Sid'
        COLUMN  ser             FORMAT  A07           Heading 'Session|Serial'
        COLUMN  aud             FORMAT  A07           Heading 'Session|AudSid'
        COLUMN  program         FORMAT  A11           Heading 'Program'    -
                                                      WORD_WRAPPED
        COLUMN  lmode           FORMAT  A04           Heading 'Lock|Mode'
        COLUMN  request         FORMAT  A04           Heading 'Rqst|Mode'
        COLUMN  type            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  'C=Share/Row-Exclusive L=RowShare '             -
                        'N=No-Lock R=RowExclusive S=Share '             -
                        'X=Exclusive '                          skip 2

        SELECT  TO_CHAR(sysdate, 'DD-MON-YY')   datevalue,
                TO_CHAR(sysdate, 'HH24:MI:SS')  timevalue,
                oracle_username      usr,
                DBA.object_name      obj,
                TO_CHAR(SES.sid)     sid,
                TO_CHAR(SES.serial#) ser,
                TO_CHAR(SES.audsid)  aud,
                SES.program,
                LCK.lmode                       ||
                '('                             ||
                DECODE(LCK.lmode,
                                0, ' ',
                                1, 'N',
                                2, 'L',
                                3, 'S',
                                4, 'R',
                                5, 'C',
                                6, 'X', ' ')    || ')' lmode,
                LCK.request                     ||
                '('                             ||
                DECODE(LCK.request,
                                0, ' ',
                                1, 'n',
                                2, 'l',
                                3, 's',
                                4, 'r',
                                5, 'c',
                                6, 'x', ' ')    || ')' request,
                LCK.type
          FROM  GV\$SESSION             SES,
                GV\$PROCESS             PRO,
                GV\$LOCKED_OBJECT       OBJ,
                DBA_OBJECTS             DBA,
                GV\$LOCK                LCK
         WHERE  LCK.sid         =  SES.sid (+)
           AND  LCK.sid         =  PRO.pid (+)
           AND  LCK.sid         =  OBJ.session_id
           AND  OBJ.object_id   =  DBA.object_id
           AND  (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
}
