Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


How to estimate database size?

Posted on 2009-04-19
Medium Priority
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
ID: 24181630
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


Accepted Solution

segurah earned 136 total points
ID: 24181672
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

Author Comment

ID: 24181754
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?

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 132 total points
ID: 24181851
<< 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

dbmullen earned 132 total points
ID: 24187783
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;


Author Comment

ID: 24191866
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
ID: 24197190
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.

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

580 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