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

Tablespace setup recommendations

Our Oracle9i database consists of 425 tables big and small. A large order-handling application built in Powerbuilder...

9 is running against the database. Currently we are using one tablespace for all tables and indexes for development. For the production environment, what would be the recommended table space setup? I definitely wouldn't recommend putting everything into one large tablespace. But I'm afraid that I can't exactly give you a recommendation on how to set up your tablespaces. What I can do is give you some guidelines on how I determine what objects I place in which tablespaces.

My main method of determining my tablespace setup is to group related objects together. For instance, I might put all of my customer order information in one tablespace and put all of my inventory product tables in another tablespace.

I also take a look at my I/O usage. With tablespaces, I can force two tablespaces to be on two separate disk volumes. If I have two tables that incur a high level of I/O, then I can put one table in one tablespace and the other table in another tablespace. Since those tablespaces are on separate disk units, I have spread my I/O out among multiple disk units.

Finally, I take a look at my backup requirements. My smallest unit of backup is the tablespace. I might backup one tablespace tonight and another tablespace tomorrow night. It all depends on my backup strategy and my backup window.

This was last published in May 2004

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.

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

Please create a username to comment.