#!/usr/bin/ksh
# whatdbprocess ()
#------------------------------------------------------------------------------#
# Module Name: whatdbprocess #
# #
# Purpose: Report on the Instance-Processes #
# #
# 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 " # whatdbprocess: Oracle Instance Processes #"
 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/whatdbprocess_$$.txt

 BREAK ON REPORT

 COMPUTE SUM OF pga_used_mem ON REPORT
 COMPUTE SUM OF pga_alloc_mem ON REPORT
 COMPUTE SUM LABEL 'Total' OF pga_max_mem ON REPORT

 COLUMN datevalue NOPRINT NEW_VALUE DATEVAR
 COLUMN timevalue NOPRINT NEW_VALUE TIMEVAR

 COLUMN spid FORMAT A6 Heading 'System|Process|ID'
 COLUMN program FORMAT A10 Heading 'Program'
 COLUMN tracefile FORMAT A29 Heading 'Tracefile'
 COLUMN latchwait FORMAT 99999999 Heading 'Latchwait'
 COLUMN latchspin FORMAT 99999999 Heading 'Latchspin'
 COLUMN pga_used_mem FORMAT 999999999 Heading 'PGA|Used|Memory'
 COLUMN pga_alloc_mem FORMAT 999999999 Heading 'PGA|Allocated|Memory'
 COLUMN pga_max_mem FORMAT 999999999 Heading 'PGA|Max|Memory'

 TTITLE CENTER 'Oracle Instance Processes' -
 LEFT DATEVAR -
 RIGHT 'Page:' FORMAT 999 SQL.pno skip 1 -
 CENTER 'whatdbprocess -- Oracle Background-Processes' -
 LEFT TIMEVAR skip 2

 SELECT TO_CHAR(sysdate, 'DD-MON-YY') datevalue,
 TO_CHAR(sysdate, 'HH24:MI:SS') timevalue,
 spid,
 program,
 tracefile,
 pga_used_mem,
 pga_alloc_mem,
 pga_max_mem
 FROM v\$process
 WHERE background = '1' 
 ORDER BY program;

 EXIT;

END_FILE

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