How to estimate database size?

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:

http://www.dba-oracle.com/t_script_oracle_table_size.htm
http://decipherinfosys.wordpress.com/2007/01/24/estimating-projecting-the-size-of-a-table-in-oracle/

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;

Select allOpen in new window

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?

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?

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.