Ask the Expert

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?

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: