How to estimate database size?

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?

Thanks.
LVL 9
yongsingAsked:
Who is Participating?
 
segurahCommented:
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: http://www.oracle-base.com/articles/10g/SpaceObjectTransactionManagement10g.php#new_segment_resource_estimation
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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;

Open in new window

0
 
yongsingAuthor Commented:
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?
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Raja Jegan RSQL Server DBA & ArchitectCommented:
<< 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.
0
 
dbmullenCommented:
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;
commit;

0
 
yongsingAuthor Commented:
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?
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.