This is used to produce a report showing the largest tables (physical size) in a database.

Parameters passed in are database name and minimum number of rows in a table to include in the report.

The report is emailed to the specified email address.

#!/bin/ksh
##############################################################################
# #
# PROGRAM NAME: table_size #
# USAGE: table_size #
# DATE WRITTEN: 15/02/2000 #
# SHORT DESCRIPTION:
# #
##############################################################################
#
#
DBNAME=$1
MINROWS=$2
MAILTO=$3
#
#
II_SYSTEM=/ingres
LD_LIBRARY_PATH=/usr/lib:$II_SYSTEM/ingres/lib
PATH=$II_SYSTEM/ingres/bin:$II_SYSTEM/ingres/utility:$PATH
export II_SYSTEM LD_LIBRARY_PATH PATH
#
if [[ -z $DBNAME ]]
then
echo ‘Usage: table_size ‘
exit
fi
if [[ -z $MINROWS ]]
then
MINROWS=100000
echo $MINROWS
fi
#
sql -f8n15.0 $DBNAME << ! >| /tmp/$DBNAME.size

SELECT table_name
, num_rows
, Total_Size = float8(number_pages) * float8(table_pagesize)
FROM iitables
WHERE table_owner = dbmsinfo(‘dba’)
AND num_rows > $MINROWS
AND table_type = ‘T’
ORDER BY Total_Size DESC;
\p\g
!
#
if [[ -z $MAILTO ]]
then
cat /tmp/$DBNAME.size | grep “|” | grep -v “+” | sed -e ‘s/|//g’
else
cat /tmp/$DBNAME.size | grep “|” | grep -v “+” | sed -e ‘s/|//g’ | mailx -s “Table Size Report for $DBNAME – tables larger than $MINROWS rows” $MAILTO
fi
#
/bin/rm -f /tmp/$DBNAME.size
#