INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Database Capacity Trend

Database Capacity Trend

(OP)
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.

CODE --> sql

#!/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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close