#!/usr/bin/ksh
# whatsessbackground ()
#-----------------------------------------------------------------------#
#    Module Name:  whatsessbackground  (c) Copyright 2012               #
#                                                                       #
#    Purpose:      Report on v$SESSION Database Background sessions.    #
#                                                                       #
#    Maintenance                                                        #
#    Date          Author        Description                            #
#    -----------   ------------  -------------------------------------  #
#    10-May-2012   M.Mullin      Module design/creation                 #
#-----------------------------------------------------------------------#
{
        DIRNAM=$(dirname $0)
        ORASID=$(echo $CONTEXT_NAME | cut -f1 -d'_')
        HNAME=$(uname -n)
                
        clear
        #---------------------------------------------------------------#
        #  TEST:  Number ($#) of arguments passed to this function?     #
        #---------------------------------------------------------------#
        if      [ $# = 0 ]
        then
            clear
            echo
            echo  " #--------------------------------------------------#"
            echo  " # whatsessbackground:  Oracle Session Information  #"
            echo  " #--------------------------------------------------#"
            echo

            #-----------------------------------------------------------#
            #  Trap: Set CNTL-C CNTL-D CNTL-\ Before Passwd Protection  #
            #-----------------------------------------------------------#
            trap        "stty echo; return"     2                              
            trap        "stty echo; return"     3
            stty -echo

            read PASSWD?" Please Enter the Oracle APPS Passwd:  "

            stty echo
            echo 

            #-----------------------------------------------------------#
            #  Trap: Reset CNTL-C CNTL-D CNTL-\ After Passwd Protection #
            #-----------------------------------------------------------#
            trap        2
            trap        3

            clear
        else
            PASSWD=$1
            clear
        fi

#-----------------------------------------------------------------------#
#  Initialize                                                           #
#-----------------------------------------------------------------------#
        SPOOLNM=/tmp/whatsessbackground_$$.txt

#-----------------------------------------------------------------------#
#  SQL*Plus ("Here-Document")                                           #
#-----------------------------------------------------------------------#
sqlplus  -s  /nolog  <<-END_FILE

        CONNECT  APPS/$PASSWD

        SPOOL ${SPOOLNM}

        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


        COLUMN datevalue NOPRINT NEW_VALUE DATEVAR
        COLUMN timevalue NOPRINT NEW_VALUE TIMEVAR

        COLUMN sprog    FORMAT A15    Heading 'ConcMgr|Program|Session'
        COLUMN pprog    FORMAT A12    Heading 'ConcMgr|Program|Process' 
        COLUMN slogn    FORMAT A9     Heading 'Connect|Date'
        COLUMN sproc    FORMAT A8     Heading 'AppsTier|Unix|Process|ID'
        COLUMN spid     FORMAT A8     Heading 'DBTier|UNIX|Process|ID'
        COLUMN serno    FORMAT 99999  Heading 'Oracle|Serial|Number'
        COLUMN type     FORMAT A08    Heading 'Status|Type'
        COLUMN osuser   FORMAT A08    Heading 'OS User'
        COLUMN username FORMAT A08    Heading 'User|Name'
        COLUMN sidno    FORMAT 99999  Heading 'Oracle|System|SID'

        TTITLE LEFT DATEVAR -
        RIGHT 'Page:' FORMAT 999 SQL.pno -
        CENTER '$(uname -n) ${ORACLE_SID} DBA Report'           skip 1 -
        LEFT TIMEVAR -
        CENTER 'whatsessbackground Oracle Sessions Assigned to' skip 1 -
        CENTER 'Database Background Processes'                  skip 2

        SELECT  TO_CHAR(sysdate, 'DD-MON-YY') datevalue,
                TO_CHAR(sysdate, 'HH24:MI-SS') timevalue,
                s.program               sprog,
                p.program               pprog,
                s.logon_time            slogn,
                s.process               sproc,
                p.spid,
                si.SID                  sidno,
                s.serial#               serno,
                SYSDATE - (s.last_call_et / 86400) "Last Call"
                FROM    v\$session s,
                        v\$process p,
                        SYS.v_\$sess_io si
        WHERE   s.paddr       = p.addr(+)
          AND   si.SID(+)     = s.SID
          AND   (   S.TYPE    = 'BACKGROUND'
                 OR S.PROGRAM LIKE '%QMN%'
                 OR S.PROGRAM  LIKE '%CJQ%');

EXIT;

END_FILE

print
print "Please see /tmp/whatsessbackground_$$.txt"
print
}