#!/usr/bin/ksh
# whatblock ()
#------------------------------------------------------------------------------#
#    Module Name:  whatlockedobject                                            #
#                                                                              #
#    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  " # whatlockedobject:           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/whatlockedobject_$$.txt

        COLUMN  datevalue       NOPRINT NEW_VALUE       DATEVAR
        COLUMN  timevalue       NOPRINT NEW_VALUE       TIMEVAR

        COLUMN  usr             FORMAT  A10           Heading 'UserName'
        COLUMN  osu             FORMAT  A08           Heading 'OS User'
        COLUMN  obj             FORMAT  A30           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  lck             FORMAT  A10           Heading 'Block|Mode'
        COLUMN  mod             FORMAT  A10           Heading 'Lock|Mode'

        TTITLE  CENTER  'Oracle Blocking Locks By User Name'            -
                LEFT    DATEVAR                                         -
                RIGHT   'Page:'         FORMAT 999 SQL.pno      skip 1  -
                CENTER  'whatlockedobject -- Sorted By LockerName and ObjectName'       -
                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,
                object_name             obj,
                s.osuser                osu,
                DECODE(l.block,
                  0, 'Not Blocking',
                  1, 'Blocking',
                  2, 'Global')          lck,
                  DECODE(v.locked_mode,
                    0, 'None',
                    1, 'Null',
                    2, 'Row-S (SS)',
                    3, 'Row-X (SX)',
                    4, 'Share',
                    5, 'S/Row-X (SSX)',
                    6, 'Exclusive', TO_CHAR(lmode)
                  )                     mod
        FROM    gv\$locked_object v,
                dba_objects d,
                gv\$lock l,
                gv\$session s
        WHERE   v.object_id = d.object_id
          AND   v.object_id = l.id1
          AND   v.session_id = s.sid
        ORDER BY usr, obj;


        EXIT;

END_FILE

echo
echo "Please see /tmp/whatlockedobject_$$.txt"
echo
}
