estimating oracle database growth over a period of time

Posted on 2009-04-25
Last Modified: 2013-12-19
scripts to interpolate/extrapolate database growth in oracle
Question by:exceed123
    LVL 23

    Expert Comment

    If you can afford the license, a very, very useful alternative is to use TOAD for DBAs.  It's a snap to set up automated capture and graphing for disk I/O, and for tablespace size changes.<input id="gwProxy" type="hidden"><!--Session data--><input onclick="jsCall();" id="jsProxy" type="hidden">
    LVL 40

    Expert Comment

    If you have AWR licensed, Oracle collects this already.


    dba_hist_tbspc_space_usage for the history of tablespace usage.

    You can join this table's tablespace_id to v$datafile.ts# for the tablespace name.
    LVL 23

    Expert Comment

    True, but my take is that the request implies an out-of-the-box solution.  The approach using script, will still require manual manipulation of the cumulative result sets.  My two cents.<input id="gwProxy" type="hidden"><!--Session data--><input onclick="jsCall();" id="jsProxy" type="hidden">
    LVL 40

    Accepted Solution

    A quick check of the price list shows Diagnostics pack is 3k per processor license, so I would be inclined to forego AWR (I don't use it) and write a script to check DBA_FREE_SPACE every 24 hours and log to a table, or for even better data, use:

    create table tblspc_hist (
      rep_time date,
      tablespace_name varchar2(200),
      mbytes integer

    -- Daily SQL to collect
    insert into tblspc_hist
    select sysdate, tablespace_name, sum(bytes) / 1000000 as mbytes
      from dba_segments
      group by sysdate, tablespace_name;

    Schedule the insert to run daily and you have a trend table.
    LVL 40

    Expert Comment

    @dvz: You are probably right. Toad is cheaper than AWR too, even with DBA addon, and certainly more useful all around. I have not used Toad for long-term trends, though, so I cannot speak as to how that works, but I will probably research it now that you pointed it out.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
    How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
    This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now