Database Capacity Trend

[tekpr00]

Hello All,

My aim is to list the current capacity for all the databases in oratab, future monthly growth rate and send to recipient email.

I know this is achievable in OEM, but our version does not have this feature.

#!/bin/ksh
#
# Script Name:  future_growth.sh
#
# ------------------------------------------------------------------------------
# Parameters: none
#
# ------------------------------------------------------------------------------
# ------------------------------------------------------------------------------
#
LASTFRIDAY=$(date --date='last Friday' +"%m%d%Y")
mv $gname.lst gname_WEEKENDING_$LASTFRIDAY.log

ALL_DATABASES=`cat /etc/oratab|grep -v "^#"|cut -f1 -d: -s`
for DB in $ALL_DATABASES
do
   unset  TWO_TASK
   export ORACLE_SID=$DB
   export ORACLE_HOME=`grep "^${DB}:" /etc/oratab|cut -d: -f2 -s`
   export PATH=$ORACLE_HOME/bin:$PATH
   echo "---> For Week Ending: `date +%m%d%Y` Database: $ORACLE_SID">$gname.lst
    sqlplus -s "/ as sysdba" <<-EOF>>gname.lst
SET LINESIZE 200
SET PAGESIZE 200
COL "Database Size" FORMAT a13
COL "Used Space" FORMAT a11
COL "Used in %" FORMAT a11
COL "Free in %" FORMAT a11
COL "Database Name" FORMAT a13
COL "Free Space" FORMAT a12
COL "Daily Growth" FORMAT a11
COL "Monthly Growth" FORMAT a20
COL "Daily Growth in %" FORMAT a16
COL "Monthly Growth in %" FORMAT a20
SELECT
(select min(creation_time) from v$datafile) "Create Time",
(select name from v$database) "Database Name",
ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2) || ' MB' "Database Size",
ROUND((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024 ),2) || ' MB' "Used Space",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 )) / ROUND(SUM(USED.BYTES) / 1024 / 1024 ,2)*100,2) || '% MB' "Used in %",
ROUND((FREE.P / 1024 / 1024 ),2) || ' MB' "Free Space",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - ((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024 )))/ROUND(SUM(USED.BYTES) / 1024 / 1024,2 )*100,2) || '% MB' "Free in %",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile),2) || ' MB' "Daily Growth",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100,3) || '% MB' "Daily Growth in %",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)*30,2) || ' MB' "Monthly Growth ",
ROUND((((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100)*30,3) || '% MB' "Monthly Growth in %"
FROM    (SELECT BYTES FROM V$DATAFILE
UNION ALL
SELECT BYTES FROM V$TEMPFILE
UNION ALL
SELECT BYTES FROM V$LOG) USED,
(SELECT SUM(BYTES) AS P FROM DBA_FREE_SPACE) FREE
GROUP BY FREE.P;
exit;
EOF
done
#cat $gname.lst >> $CHECKFILE
mailx -s "Database $ORACLE_SID growth report" recepent@mail.com < $gname.lst

The result should have the following format:
---> For Week Ending: 01272017 Database: sids
Create Time Database Name Database Size Used Space Jan Monthly Growth Feb Monthly Growth Match Monthly Growth
---------- ---------------- ------------------------------------------------------------------------

01-JUN-16 SIDS 107494 MB 52949 MB 49.26% MB 54545.38 MB 50.74% MB .205% MB 6.149% MB

---> For Week Ending: 01272017 Database: sid2
Create Time Database Name Database Size Used Space Jan Monthly Growth Feb Monthly Growth March Monthly Growth
---------- ---------------- ------------------------------------------------------------------------

01-JUN-18 SID2 107494 MB 52949 MB 49.26% MB 54545.38 MB 50.74% MB .205% MB 6.149% MB

-------
-------

Please help, and I will be appreciative.

Thanks,
Tekpr00