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.

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.