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

Query that shows database Growth Trend over 3 months.

Query that shows database Growth Trend over 3 months.

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

CODE --> sql

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:

CODE

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?

RE: Query that shows database Growth Trend over 3 months.


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

CODE

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

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

RE: Query that shows database Growth Trend over 3 months.

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:

CODE

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.

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

RE: Query that shows database Growth Trend over 3 months.


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

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

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