Q

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

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close