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