Problem solve Get help with specific problems with your technologies, process and projects.

Calculating space used by one table and index

Are there any formulas for calculating the space to be used by one table and index?

Soon, one system is going to move to a production database. Are there any formulas for calculating the space to be used by one table and index? i.e., Sum all columns from each table, the amount of records per month, give it a block size and projection time like one year = initial, next and maxtents... At the end all tables are the space of the tablespace for X application.

There used to be these complicated spreadsheets that DBAs would use to calculate sizes of indexes and tables. But in the end, this is just too much work so those spreadsheets are pretty hard to come by now.

When people want to know how much space will be used, I tell them to load a representative sample of their data, maybe 10% of what they expect. Then, use the DBMS_SPACE package to see exactly how much space the tables and indexes are using for that 10% of the data. Multiply by the appropriate factor (10 in our case) to see what the total space would be. The DBMS_SPACE package has the ability to look inside the space allocation to report how much is being used.

From what it sounds like, you already have data loaded into tables. If you know that this data represents one month's worth of processing, then run DBMS_SPACE and multiply the figures by 12 for one year's worth of data.


For More Information

Dig Deeper on Oracle database design and architecture

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.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.