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 someone 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 ###

#####################