Ask the Expert

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?

    Requires Free Membership to View

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 first published in May 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: