Query that shows database Growth Trend over 3 months.

[tekpr00]

Hello All:
Here is a attempted script that should should show the rate of the database growth over a 3 month trend. Also it shows the final average growth. Though it run, it is not showing all output. I suspect this is due to formatting. Please all input will be appreciated.

sqlplus -s /nolog >> $LOGFILE << EOF
CONNECT / AS SYSDBA
set linesize 150 pagesize 8000 feedback off heading off
column tablespace_name format a20
column month  format a20
column SIZE_MB2  format a30
column SIZE_MB1  format a30
column SIZE_MB  format a30

create table stats$segment_info as
select
sysdate snap_time,owner, segment_name,segment_type ,tablespace_name,sum(bytes) bytes from dba_segments group by owner,segment_type, segment_name,tablespace_name;
commit;
-- Run the following once a calendar month via dba_jobs.

insert into stats$segment_info
select
 sysdate snap_time,owner, segment_name,segment_type , tablespace_name,sum(bytes) bytes from dba_segments group by owner,segment_type, segment_name,tablespace_name;
commit;
select tablespace_name,
      to_char(snap_time,'MON YYYY') Month,
       max(decode(trunc(snap_time,'MON'),trunc(add_months(sysdate,-2),'MON'),size_m,0))  size_mb2
      ,max(decode(trunc(snap_time,'MON'),trunc(add_months(sysdate,-1),'MON'),size_m,0))  size_mb1
      ,max(decode(trunc(snap_time,'MON'),trunc(sysdate,'MON'),size_m,0))  size_mb
       ,100*(max(decode(trunc(snap_time,'MON'),trunc(sysdate,'MON'),size_m,0))
     -    max(decode(trunc(snap_time,'MON'),trunc(add_months(sysdate,-1),'MON'),size_m,0)))
                 /max(decode(trunc(snap_time,'MON'),trunc(add_months(sysdate,-1),'MON'),size_m,1)) growth from
                  (select  tablespace_name, snap_time
     ,sum(bytes)/1024/1024 size_M
from stats$segment_info
  where snap_time > trunc(add_months(sysdate,-2),'MON')-2
  and tablespace_name not in ('SYSAUX','SYSTEM','UNDO','UNDOTBS1')
group by tablespace_name,snap_time) a
group by tablespace_name,snap_time
order by tablespace_name;
drop table stats$segment_info;
exit
EOF


if [ `cat $LOGFILE | wc -l` -gt 0 ]
  then
    echo "Here is the database growth for the instance $ORACLE_SID:" >  $CHECKFILE
    echo ""                                                                          >> $CHECKFILE
    echo "TABLESPACE_NAME  MONTH                SIZE_MB2   SIZE_MB1     SIZE_MB     GROWTH             "           >> $CHECKFILE
    echo "---------------- -----                ---------  --------     -------     ----               "           >> $CHECKFILE
    cat $LOGFILE >> $CHECKFILE
    /usr/bin/mailx -s "Database $ORACLE_SID growth report" sample@email.com < $CHECKFILE
fi

rm $CHECKFILE $LOGFILE

Here is the output:

Here is the database growth for the instance ppd:

TABLESPACE_NAME      MONTH                SIZE_MB2   SIZE_MB1     SIZE_MB     GROWTH             
----------------     -----                ---------  --------     -------     ----               

CWMLITE 	     MAY 2013		  ########## ########## ##########	 1550
CWMLITE 	     MAY 2013		  ########## ########## ##########	 1550
DRSYS		     MAY 2013		  ########## ########## ##########    2593.75
DRSYS		     MAY 2013		  ########## ########## ##########    2593.75

How can I get the ########## to show?

 

[LKBrwnDBA]

This: "...,sum(bytes) bytes..." is NOT equivalent to MB.
Use:

...,ROUND(sum(bytes)/1024/1024,0) bytes...

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

TekPro,

When you receive "##########" or something similar, it means that the SQL*Plus FORMAT for the column does not allow enough characters to display the output.

Notice in your column definitions you use the format masks:

column SIZE_MB2  format a30
column SIZE_MB1  format a30
column SIZE_MB  format a30

Since the output for those three columns are numeric, the "A30" (alphanumeric) mask is inappropriate for your output. I recommend using a mask of perhaps "999,999,999,999,999". That should give you readable output.

Try that and give us an update.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[LKBrwnDBA]

PS: Enterprise Manager has a "Reports" section where you can get nice reports on storage, etc...


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb