#!/bin/ksh
# -------------- AKADIA AG  ----------------------------------------------------
#
# Funktion                                                      letzte Aenderung
# ------------------------------------------------------------- ----------------
# Anzeige des freien Tablespace-Platzes                         28.12.92  M.Zahn
# Loesung mit awk, da Join sehr, sehr
# langsam ist !
# ------------------------------------------------------------------------------
trap 'cleanup' 1 2 3 5 15

cleanup()
{
	echo "Cleaning up ..."
	test -f /tmp/total$$.lst && rm -f /tmp/total$$.lst
	test -f /tmp/free$$.lst  && rm -f /tmp/free$$.lst
	exit
}

print -n "Enter Password for SYSTEM Account: "
read system_pwd
echo "Counting free space for ORACLE Database: '$ORACLE_SID' on '`uname -n`'"
echo ""

$ORACLE_HOME/bin/sqlplus -s system/$system_pwd <<-EOF 1>/dev/null 2>&1
	set termout off
	set feed off
	set pagesize 0

	break on tablespace_name
	column tablespace_name format A32

	select	tablespace_name,
		sum(bytes)
	from dba_data_files
	group by
		tablespace_name
	order by
		tablespace_name	

	spool /tmp/total$$ 
	/
	spool off

	select sum(bytes)
	from dba_free_space
	group by
		tablespace_name
	order by
		tablespace_name

	spool /tmp/free$$ 
	/
	spool off
EOF

paste /tmp/total$$.lst /tmp/free$$.lst |
awk ' BEGIN {
	printf "Tablespace      Total      Available   Used    Capacity\n"
	printf "                [KB]       [KB]        [KB]    [%]\n"
	printf "-------------------------------------------------------\n"
      }
      {
	printf "%-10s %10d %10d %10d %5d\n", $1, \
				        $2/1000, \
				        $3/1000, \
				        ($2-$3)/1000, \
				        ($3*100)/$2
      } '

cleanup