#!/usr/bin/ksh
# whatoid ()
#------------------------------------------------------------------------------#
#    Module Name:  whatoid                                                     #
#                                                                              #
#    Purpose:      Report on the Active OID Process(s).                        #
#                                                                              #
#    Maintenance                                                               #
#    Date          Author        Description                                   #
#    -----------   ------------  --------------------------------------------  #
#    10-Oct-2009   T.Robinette   Module design/creation                        #
#                  F.Bender      Module design/creation                        #
#                  A.Estrada     Module design/creation                        #
#                  M.Barone      Module design/creation                        #
#------------------------------------------------------------------------------#
{
        DIRNAM=$(dirname $0)
        ORASID=$(echo $CONTEXT_NAME | cut -f1 -d'_')
        HNAME=$(uname -n)
                
	clear
	echo
	echo  " #--------------------------------------------------------#"
	echo  " # whatoid:    Oracle10g Application Server Process Table #"
	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/whatoid_$$.txt

        COLUMN  datevalue       NOPRINT NEW_VALUE       DATEVAR
        COLUMN  timevalue       NOPRINT NEW_VALUE       TIMEVAR

	COLUMN instance		FORMAT 99999
	COLUMN pid		FORMAT 99999
	COLUMN serverid		FORMAT 99999
	COLUMN flags		FORMAT A15 
	COLUMN hostname		FORMAT A10
	COLUMN configset	FORMAT 99999
	COLUMN state		FORMAT 99999
	COLUMN lastwaketime	FORMAT A9
	COLUMN retrycount	FORMAT 99999

        TTITLE  CENTER  '[7m${ORANAM} (${HNAME}) Apps SQLText[0m'       -
                LEFT    DATEVAR                                     skip 1  -
                LEFT    TIMEVAR                                     skip 2

       SELECT	TO_CHAR(sysdate, 'DD-MON-YY')           datevalue,
		instance, pid, serverid, flags, hostname,
		configset, state, lastwaketime
	FROM	ODS.ods_process;

EXIT;

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