!/usr/bin/ksh
# whatsession ()
#-----------------------------------------------------------------------#
#    Module Name:  whatsessionprocess                                   #
#                                                                       #
#    Purpose:      Report on the Database Sessions and Processes.       #
#                                                                       #
#    Maintenance                                                        #
#    Date          Author        Description                            #
#    -----------   ------------  -------------------------------------- #
#    10-May-2012   M.Barone      Module design/creation                 #
#-----------------------------------------------------------------------#
{
        DIRNAM=$(dirname $0)
        ORASID=$(echo $ORACLE_SID)
        HNAME=$(uname -n)
                
        clear
        echo
        echo  " #-------------------------------------------------------#"
        echo  " # whatsession:     Oracle Apps Sessions/Processes Area  #"
        echo  " #-------------------------------------------------------#"
        echo
        echo  " Connecting as SYSDBA"
        sleep 2

        clear
        ORANAM=${ORASID}

        ORD_LNE="PROC.username, SESS.username, PROC.spid, SESS.sid"
        ORD_LNE="${ORD_LNE}, SESS.serial#, SESS.status"

        SQL_LNE="ORDER BY ${ORD_LNE}"

#-----------------------------------------------------------------------#
#  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        80
        set     pagesize        999

        SPOOL /tmp/whatsession_$$.txt

        COLUMN  datevalue       NOPRINT NEW_VALUE       DATEVAR
        COLUMN  timevalue       NOPRINT NEW_VALUE       TIMEVAR

        COLUMN  PROC_username   FORMAT  A6      Heading 'Unix|User'
        COLUMN  SESS_username   FORMAT  A6      Heading 'Oracle|User'
        COLUMN  PROC_spid       FORMAT  A8      Heading 'Unix|ID'
        COLUMN  SESS_sid        FORMAT  99999   Heading 'Oracle|Session'
        COLUMN  SESS_serial#    FORMAT  99999   Heading 'Session|Serial'
        COLUMN  SESS_status     FORMAT  A8      Heading 'Session|Status'
        COLUMN  SESS_terminal   FORMAT  A12     Heading 'User|Terminal'
        COLUMN  SESS_module     FORMAT  A18     Heading 'Session|Module'

        BREAK   ON PROC_username skip 1 ON SESS_username skip 0         -
                ON SESS_status   skip 0 ON SESS_sid      skip 0         -
                ON SESS_serial#  skip 0 ON PROC_spid     skip 0

        TTITLE  CENTER '${ORANAM} ${HNAME} EBusiness Session/Processes' -
                LEFT    DATEVAR                                         -
                RIGHT   'Page:'         FORMAT 999 SQL.pno      skip 1  -
                CENTER  'whatsession -- Sorted by UserName, UnixiID'    -
                LEFT    TIMEVAR                                 skip 1

        SELECT  TO_CHAR(sysdate, 'DD-MON-YY')           datevalue,
                TO_CHAR(sysdate, 'HH24:MI:SS')          timevalue,
                PROC.username                   PROC_username,
                SESS.username                   SESS_username,
                PROC.spid                       PROC_spid,
                SESS.sid                        SESS_sid,
                SESS.serial#                    SESS_serial#,
                SESS.status                     SESS_status,
                SESS.terminal                   SESS_terminal,
                SESS.module                     SESS_module
          FROM  sys.v\$process  PROC,
                sys.v\$session  SESS
         WHERE  PROC.addr     = SESS.paddr  (+)
           AND  SESS.username   IS NOT    NULL
         ${SQL_LNE};
EXIT;

END_FILE
print
print "Please see /tmp/whatsessionprocess_$$.txt"
print
}