HTML space report

Have you ever received a request from the Business people about the space usage of a database to be delivered to their inboxes on a daily basis, well yup, I did and since it had come from some one in real high authority - I decided to generate a pleasant report of the usage of database.

I wrote a shell script that has an in-built sql script which retrieves information from the dba_* views. This result is spooled as an html file and sent to the recipient(s) as an attachment. The shell script takes the ORACLE_SID (database name) as the command line argument. Please review the script once after pasting it into your editor of choice for editting/spacing errors.

####################################################
# Name : space_report.ksh
#
# Usage : ksh space_report.ksh ORACLE_SID
#
####################################################

#!/usr/bin/ksh

if [ "$#" -lt 1 ]
then
echo " \nUsage : ksh space_report.ksh ORACLE_SID "
echo "\n"
exit 1
fi

export ORATAB=/var/opt/oracle/oratab   ## change this per your env.
export date_string=`date '+%d-%b-%Y'`
export ORACLE_SID=$1
export ORACLE_HOME=`grep $ORACLE_SID $ORATAB|head -1|awk -F":" '{print $2}'`
export To_id=to_id@oracleandunix.blogspot.com
export Cc_id=cc_id@oracleandunix.blogspot.com
export LOG_DIR=$HOME    
export LOG_FILE=$LOG_DIR/${ORACLE_SID}_space_report_${date_string}.html
export CAP_ORACLE_SID=`echo $ORACLE_SID|tr [:lower:] [:upper:]`


$ORACLE_HOME/bin/sqlplus -S "/as sysdba" << E_O_F

set markup html on
spool $LOG_FILE
set pages 50
set lines 100
set echo off
set serveroutput on
set feed off
col "TABLESPACE" format a25
col "FREE" format 9999999.99
col "MAX_FREE" format 9999999.99
col "NEXT_EXT" format a8
col "% Full" format 9999
col TOTAL format 999999.99
col USED format 999999.99
col SIZE format 9999.99

select name "DB NAME", to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') "REPORT RUN TIME" 
from v$database;
prompt
prompt
select substr(t.tablespace_name,1,25) "TABLESPACE",
decode(b.EXTENT_MANAGEMENT,'LOCAL','L','DICTIONARY','D',b.EXTENT_MANAGEMENT) 
"EXT MGMT",
t.totspace/1024/1024  "TOTAL (MBs)",
round((t.totspace-sum(f.bytes))/1024/1024,2) "USED (MBs)",
round(sum(f.bytes)/1024/1024,2) "FREE (MBs)",
round(max(f.bytes)/1024/1024,2) "MAX FREE (MBs)",
round(((t.totspace-sum(f.bytes)) / t.totspace)*100) "% Full"
from dba_free_space f, dba_tablespaces b,
(select tablespace_name,sum(bytes) totspace
from dba_data_files
group by tablespace_name) t
where t.tablespace_name=f.tablespace_name
and t.tablespace_name=b.tablespace_name
group by t.tablespace_name,t.totspace,
b.next_extent,b.EXTENT_MANAGEMENT,b.tablespace_name
order by b.tablespace_name
prompt
prompt
prompt
select max('TOTAL SIZE OF THE DATABASE IS            ') "DESCRIPTION",
rpad(round(sum(bytes)/1024/1024/1024,2),5,'0')||' GBs' "SIZE"
from dba_Data_Files
union all
select max('USED SIZE OF THE DATABASE IS             '),
rpad(round(sum(bytes)/1024/1024/1024,2),5,'0')||' GBs'
from dba_segments
union all
select max('FREE SPACE AVAILABLE IN THE DATABASE IS  '),
rpad(round(sum(bytes)/1024/1024/1024,2),5,'0')||' GBs'
from dba_free_space;
prompt
prompt
E_O_F

(
cat << E_O_F
Space report for $ORACLE_SID Database
E_O_F
echo "\n"
/usr/bin/uuencode $LOG_FILE $LOG_FILE
)|mailx -s "Space Report - $CAP_ORACLE_SID" -c "$Cc_id" $To_id

#####################
### END OF SCRIPT ###
#####################