#!/usr/bin/ksh
# whatjvmsession ()
#------------------------------------------------------------------------------#
#    Module Name:  whatjvmsession  (c) 2012                                    #
#                                                                              #
#    Purpose:      Report on the v$SESSION used by JVM/JDBC Connections.       #
#                                                                              #
#    Maintenance                                                               #
#    Date          Author        Description                                   #
#    -----------   ------------  --------------------------------------------  #
#    12-May-2009   M.Barone      Module design/creation                        #
#------------------------------------------------------------------------------#
{
        DIRNAM=$(dirname $0)
        ORASID=$(echo $CONTEXT_NAME | cut -f1 -d'_')
        HNAME=$(uname -n)

        #----------------------------------------------------------------------#
        #  TEST:  Number ($#) of arguments passed to this function?            #
        #----------------------------------------------------------------------#
        if      [ $# = 0 ]
        then
            clear
            echo
            echo  " #--------------------------------------------------------#"
            echo  " # whatjvmsession:          Oracle Apps JVM/JDBC Sessions #"
            echo  " #--------------------------------------------------------#"
            echo

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

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

            stty echo
            echo
        fi

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

        CONNECT APPS/$PASSWD

        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/whatjvmsession_$$.txt

        COLUMN datevalue NOPRINT NEW_VALUE DATEVAR
        COLUMN timevalue NOPRINT NEW_VALUE TIMEVAR
        COLUMN machine	FORMAT A12      Heading 'Machine'
        COLUMN process	FORMAT A8       Heading 'Unix|Process'
        COLUMN program	FORMAT A24      Heading 'JVM/JDBC Program'
        COLUMN module	FORMAT A24      Heading 'JVM/JDBC Module'
        COLUMN pcount	FORMAT 9999999  Heading 'Count'

	BREAK  on process skip 1
	COMPUTE SUM of count(*) on process

        TTITLE LEFT DATEVAR -
        RIGHT 'Page:' FORMAT 999 SQL.pno -
        CENTER '$(uname -n) ${ORACLE_SID} DBA Report'   skip 1 -
        LEFT TIMEVAR -
        CENTER 'Oracle Current JVM/JDBC Session(s)'     skip 1 -
        CENTER 'whatjvmsession'                         skip 2

        SELECT  TO_CHAR(sysdate, 'DD-MON-YY') datevalue,
                TO_CHAR(sysdate, 'HH24:MI-SS') timevalue,
		machine,
		process,
		program,
		module,
		count(*) pcount
	FROM	v\$session
	WHERE	UPPER(program) like 'JDBC%'
	AND	username = 'APPS'
	AND	process is not null
	GROUP BY machine, process, program, module;

EXIT;
END_FILE

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