oracle@sawftdev:/home/oracle> ./check_connection_pools.sh

HOSTNAME:       sawftdev
DATE:           Tue Dec 29 11:03:44 EST 2009

Connection Pool Name          In Use        Free       Total        Wait         Max Max Allowed
--------------------          ------        ----       -----        ----         --- -----------
AIAConnectionPool                  2           3           5           0           5          50
BPELPM_CONNECTION_POOL             0           5           5           0          15          50
ESBAQJMSPool                       0           3           3           0           3          20
ESBPool                            0           5           5           0           5          50

#################################################################################
#
# AUTHOR:            Ahmed Aboulnaga
# FILENAME:          check_connection_pools.sh
# DESCRIPTION:       Check connection pools
# CREATION DATE:     2009-12-29
# LAST UPDATE DATE:  2009-12-29
# REQUIREMENTS:      ORACLE_HOME must be set
#
#################################################################################


#----------------------------------------
# Check for ORACLE_HOME and binary
#----------------------------------------

if [ "${ORACLE_HOME}" == "" ]; then
  echo ""
  echo "ERROR: Environment variable ORACLE_HOME is not set."
  echo ""
  exit
elif [ ! -f $ORACLE_HOME/bin/dmstool ]; then
  echo ""
  echo "ERROR: File 'dmstool' does not exist in this ORACLE_HOME."
  echo ""
  exit
fi


#----------------------------------------
# Display runtime connection pool information
#----------------------------------------

$ORACLE_HOME/bin/dmstool -list > /tmp/dmstool.tmp

echo ""
echo "HOSTNAME: `hostname`"
echo "DATE:             `date`"
echo ""
echo "Connection Pool Name          In Use        Free       Total        Wait         Max Max Allowed"
echo "--------------------          ------        ----       -----        ----         --- -----------"

for i in `cat /tmp/dmstool.tmp | grep "FreePoolSize.value" | grep -v "Example Connection Pool" | grep default | grep oc4j`
do
  export POOLNAME=`echo $i | cut -f6 -d/`
  if [ "${POOLNAME}" != "" ]; then

    # Free Connections
    export FREEPOOLSIZE=`$ORACLE_HOME/bin/dmstool -count 1 $i | grep "${POOLNAME}" | awk '{print $2}'`

    # Totals Connections in Pool
    export T=`cat /tmp/dmstool.tmp | grep ${POOLNAME} | grep "/PoolSize.value"`
    export TOTALACTIVE=`$ORACLE_HOME/bin/dmstool -count 1 $T | grep ${POOLNAME} | awk '{print $2}'`

    # Threads Waiting for Connections
    export T=`cat /tmp/dmstool.tmp | grep ${POOLNAME} | grep "/WaitingThreadCount.value"`
    export THREADSWAITING=`$ORACLE_HOME/bin/dmstool -count 1 $T | grep ${POOLNAME} | awk '{print $2}'`

    # Maximum Pool Size Reached
    export T=`cat /tmp/dmstool.tmp | grep ${POOLNAME} | grep "/PoolSize.maxValue"`
    export MAXPOOLSIZEREACHED=`$ORACLE_HOME/bin/dmstool -count 1 $T | grep ${POOLNAME} | awk '{print $2}'`

    # Maximum Pool Size Allowed
    export T=`cat /tmp/dmstool.tmp | grep ${POOLNAME} | grep "/PoolSizeUpperBound.value"`
    export MAXPOOLSIZEALLOWED=`$ORACLE_HOME/bin/dmstool -count 1 $T | grep ${POOLNAME} | awk '{print $2}'`

    # Connections In Use
    export INUSE=`expr ${TOTALACTIVE} - ${FREEPOOLSIZE}`

    export DISPLAY_POOLSTRLENGTH=`expr length "${POOLNAME}"`
    export DISPLAY_TAB=""
    if [ $DISPLAY_POOLSTRLENGTH -lt 13 ]; then
      export DISPLAY_TAB="              "
    fi

    printf "%-24s%12s%12s%12s%12s%12s%12s" ${POOLNAME} ${INUSE} ${FREEPOOLSIZE} ${TOTALACTIVE} ${THREADSWAITING} ${MAXPOOLSIZE
REACHED} ${MAXPOOLSIZEALLOWED}
    echo ""

  fi
done
echo ""

rm -f /tmp/dmstool.tmp