This script calculates the size of all databases in the oratab file if no arguments are passed or the size of a specific database if an argument is passed.The first argument $1 is considered to be the instance name. All other arguments are ignored.


#! /bin/ksh

PROGRAM_NAME=`basename $0`
PROG_BIN_DIR=/users/oracle/local/prod/sh
PROG_LOG_DIR=/users/oracle/local/prod/logs
PATH=${PROG_BIN_DIR}:/users/oracle/product/10.2.0/db/bin:/usr/bin:/users/oracle/bin:/usr/local/bin:/sbin:/usr/sbin:/usr/ccs/bin:/usr/ucb:/usr/bin/X11:/usr/bin:/usr/proc/bin:/usr/ucb:/usr/local/bin
ORATABFILE=/etc/oratab
# If the first argument to this script is null, then calculate the size all databases in the oratab file
if [[ -z $1 ]]; then
# Get all SIDs from oratab file
for dbname in `sed -e '/^[ ]*$/d' -e '/^[ ]*\#/d' -e '/^[ ]*\*/d' ${ORATABFILE}
cut -d":" -f1
sed 's/ //'
grep -v '\+ASM'` ; do
RUNDATE=`date '+%m%d%Y_%H%M%S'`
# Continue with next SID when instance is not running
if [[ `ps -ef
grep "ora_smon_${dbname}"
grep -v 'grep'
wc -l
awk '{ print $1 }'` -eq 0 ]]; then
echo "Database Name ${dbname} is not up and running"
continue
fi
# Change the Oracle environment for the current dbname
ORACLE_SID=${dbname}
ORAENV_ASK=NO
. oraenv
ORAENV_ASK=YES
PATH=${ORACLE_HOME}/bin:${PATH}
LIBPATH=${ORACLE_HOME}/lib


echo "Database Size of ${dbname} = \c"
sqlplus -s / <<-EOF
WHENEVER SQLERROR EXIT FAILURE
SET PAGES 0 HEA OFF FEED OFF VER OFF
--
SELECT TO_CHAR(ROUND(SUM(bytes)/1024/1024/1024, 2))
' GB'
FROM
(
SELECT SUM(bytes) bytes
FROM dba_data_files
UNION
SELECT SUM(bytes) bytes
FROM dba_temp_files
)
/
EXIT
EOF
RETCODE=$?



if [[ ${RETCODE} != 0 ]]; then
echo "FAILURE : Calculating Database Size of Database ${dbname} On ${RUNDATE}"
continue
fi
done

# If the first argument to this script is not null, then calculate the size of the database specified as the argument
else
dbname=`echo $1
tr "[A-Z]" "[a-z]"`
RUNDATE=`date '+%m%d%Y_%H%M%S'`


# Exit when dbname does not exist in oratab file
# This is required because oraenv file is being called later
# and if dbname is missing from oratab file, the oraenv script
# will prompt for ORACLE_HOME




if [[ `grep "^${dbname}:" ${ORATABFILE}
wc -l
awk '{ print $1 }'` -eq 0 ]]; then
echo "Database Name ${dbname} does not exist in ${ORATABFILE} file"
exit 16
fi


# Exit when instance is not running
if [[ `ps -ef
grep "ora_smon_${dbname}"
grep -v 'grep'
wc -l
awk '{ print $1 }'` -eq 0 ]]; then
echo "Database Name ${dbname} is not up and running"
exit 16
fi






# Change the Oracle environment for the current dbname
ORACLE_SID=${dbname}
ORAENV_ASK=NO
. oraenv
ORAENV_ASK=YES
PATH=${ORACLE_HOME}/bin:${PATH}
LIBPATH=${ORACLE_HOME}/lib




echo "Database Size of ${dbname} = \c"
sqlplus -s / <<-EOF
WHENEVER SQLERROR EXIT FAILURE
SET PAGES 0 HEA OFF FEED OFF VER OFF
--
SELECT TO_CHAR(ROUND(SUM(bytes)/1024/1024/1024, 2))
' GB'
FROM
(
SELECT SUM(bytes) bytes
FROM dba_data_files
UNION
SELECT SUM(bytes) bytes
FROM dba_temp_files
)
/
EXIT
EOF
RETCODE=$?






if [[ ${RETCODE} != 0 ]]; then
echo "FAILURE : Calculating Database Size of Database ${dbname} On ${RUNDATE}"
fi
fi