How to estimate database size?

Posted on 2009-04-19
Last Modified: 2013-12-18
We have an Oracle database 10g on Solaris platform. There are dozens of tables in it. We would like to estimate the disk space growth per year for the database. If we have table definition for each table, as well as the estimated number of rows added per table per year, are we able to estimate the disk space growth per year?

Question by:yongsing
    LVL 57

    Expert Comment

    by:Raja Jegan R
    In the query given below, 1000000  indicates that the no of records in tables after 1 year would be 1000000 records. Kindly change it to your expected value which gives you the result.

    Some other methods below:

    Hope this helps
    select table_name table, (sum (data_length) / 1048576) * 1000000 "Length MB"
    from dba_tab_columns
    where owner = 'owner'
    group by table_name;

    Open in new window

    LVL 6

    Accepted Solution

    Tehorically Yes!, each table has fields, each field have a data type, each data type has a a size.

    Then, each record sizes : sum of each size of each data type in one table.

    But probably you have not fixed size records (as varchard) then, the maximum space you will need will be: select sum(bytes) from user_segments where segment_name = 'YOUR_TABLE_NAME';

    If your DB is running and have good sample data, more realistic can be: select avg_row_len * num_rows * (1 + PCT_FREE/100) * 1.15
    from dba_tables where table_name = 'YOUR_TABLE_NAME';

    More info in:
    LVL 9

    Author Comment

    Does the following query return the maximum row length for each table?

    select table_name, sum (data_length)
    from dba_tab_columns
    group by table_name
    order by 1;

    If I expect the table to grow by 1000 rows per year, can I multiply the maximum row length by 1000 to get the disk usage per year? Is it so simple?

    What about other factors like blocks, which I read about in the articles?
    LVL 57

    Assisted Solution

    by:Raja Jegan R
    << can I multiply the maximum row length by 1000 to get the disk usage per year? Is it so simple? >>

    Yes. You are correct, But you need to do this conversion (sum (data_length) / 1048576) to get the size in MB, otherwise it would be in Bytes.

    Blocks and Segments refers to the physical allocations of disk for that table. By default each and every table will be having some free space to accommodate upcoming records and hence will have some free space assigned for it. Hence your actual table size is the one which you got before in addition to the free space reserved for that table.

    The methods in that article helps you to find the No of used and reserved blocks available for a table.
    LVL 10

    Assisted Solution

    select segment_name, bytes from dba_segments where owner = 'OWNER'
      and segment_name in (....)

    that gives you the size today
    use math to determine how average bytes per row
    use math to determine how big it was with different number of rows.

    weekly, we copy dba_segments to another database and keep it over time.
    insert into schema1.dba_segments_time as select 'REMOTE_DB', A.*, sysdate from dba_segments@remote_db a;

    LVL 9

    Author Comment

    Hi rrjegan17,

    Let's say that I have a table called ABC. Initially, when created, it has zero records. After that, it is estimated that 1000 new records will be inserted into this table every year. So you are saying that the query below will tell me the disk size growth for this table for every year?

    select table_name, (sum (data_length) / 1048576) * 1000
    from dba_tab_columns
    where table_name = 'ABC'
    group by table_name;

    What about indexes? Shouldn't we estimate for indexes as well?
    LVL 57

    Expert Comment

    by:Raja Jegan R
    If you have zero records now, then it would say it as 0 after 1 year too. You should have some random occurences of records to estimate your Data_length correctly.

    This will give the size estimates for your indexes:

    select sum(bytes) from dba_segments
    where segment_type="INDEX";

    Again you need to have some sample values for the calculation to give out some valid result.

    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.

    Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
    This post first appeared at Oracleinaction  ( Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

    737 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

    15 Experts available now in Live!

    Get 1:1 Help Now