Q

Calculating table size

How do I estimate or calculate the size of a table, knowing its volume or the rate of increase of its volume? It

used to be that Oracle DBAs had complex spreadsheets that would add up all of the overhead, take into account all of the variables, and then be able to tell you how big a table would be once you stored that data in the database. But that is simply too much work. Your time is too valuable to be using spreadsheets in this manner. Disk units are much cheaper these days and these old spreadsheets would cost your employer more to use than to just purchase more than enough disk to satisfy your needs. In some cases, disk vendors sell you more than you need anyway.

The solution more often employed is to load a small sample of your data into a table, say 100 rows of representative data. Then use the DBMS_SPACE package to determine how much space those 100 rows take up. If you know that you will have 1 million rows in the end, then multiply the space you calcuated above by 10,000 to figure out how much space 1 million rows would require. Or, you could compute statistics on that table, query DBA_TABLES for the AVG_ROW_LEN and multiply by 1 million rows.

Knowing the rate of growth is a different thing, but still very important. Oracle does have a Capacity Planner option for its Enterprise Manager product. The Capacity Planner will take a snapshot of your current system disk needs. Then after some time, you cake another snapshot. Knowing the difference in sizes and the time span, you can easily calculate the growth rate. The Capacity Planner is part of OEM's Diagnostic Pack and costs extra money. If you do not use this, then you can create your own custom grown system.

This was first published in March 2004

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close