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.