Should an Oracle database have more than one tablespace for data storage?

Question

My team maintains an Oracle database that is approx. 200GB in size. All of the data (tables, indexes, etc) lives inside a single 'USERS' tablespace. Is this a bad idea? What benefits are there to having multiple tablespaces, and under what circumstances would I want to add more to my database?

Thanks!

Answer 1

See http://download.oracle.com/docs/cd/B10501%5F01/server.920/a96521/tspaces.htm

See http://download.oracle.com/docs/cd/B28359%5F01/server.111/b28318/physical.htm

You can use multiple tablespaces to perform the following tasks:

Control disk space allocation for database data

Assign specific space quotas for database users

Control availability of data by taking individual tablespaces online or offline

Perform partial database backup or recovery operations

Allocate data storage across devices to improve performance

Answer 2

S. Lott already gave a good list of general reasons why one might want to split that up onto multiple tablespaces.

More specific to your situation...

I would ask myself if there are specific reasons to change things now. It's no small task to make a structural change like that. Are there performance issues? Are you running against storage space limits? Do you need to assign space quotas? Does your present backup and restore plan meet your needs?

If you could go back in time and redo things from the beginning you would certainly want to plan to sensibly divide the database into different table spaces. But is it worth it now?

Answer 3

My bias (and this is largely a matter of personal preference) is that if there is no compelling benefit to creating additional tablespaces, life is easier with a single tablespace.

• There is no performance benefit to putting objects in different tablespaces. There is an old myth that separating tables and indexes would have some performance benefits. There is a potential benefit to spreading I/O over all available spindles, but that's better done with multiple data files in a single tablespace then with multiple tablespaces since Oracle does a round-robin allocation of extents in different data files assuming that your SAN isn't already doing something to even out I/O.
• If you have large, static lookup/ history tables such that you could bring a new copy of the database to the client site by just bringing the smaller transactional tablespaces, that would be a reason to consider multiple tablespaces. But there are very few applications that have this sort of setup. If you'll have to bring all 200 GB, it doesn't matter how many tablespaces you have.
• Along the same lines, if you have large read-only objects, putting them in a read-only tablespace can vastly decrease the time and space required for backups. Again, though, this isn't particularly common in practice outside of data warehouses.
• If your application could run without some subset of objects, there may be a benefit to creating separate tablespaces so that you could take one offline and do a tablespace-level restore. Again though, few applications could run without a set of objects-- if you lose the index tablespace, for example, the application is likely just as dead as had you lost everything.
• If you have a large number of empty or mostly empty tables and a number of very large tables, separate tablespaces with different extent allocation policies may be preferrable from a space utilization standpoint. This happens occasionally with packaged apps where any given installation is using a relatively small percentage of the available tables and you don't want each of the empty tables to have a relatively large extent assigned to it. With automatic extent management in a locally managed tablespace, this tends not to be a major concern, it may be more concerning if you want to use uniform extents.
• If different objects have different priorities for disk performance, and you have different types of disk available, separate tablespaces can allow you to put different objects on different sets of disks. In a data warehouse, for example, you may want to put older data on slower, cheaper disk and newer data on more costly disk. This doesn't happen much with OLTP applications.

Unless your application falls into one of these special cases, the only benefit to having separate tablespaces is to appeal to a DBA's sense of organization. Personally, I'm more than happy to be able to avoid specifying a tablespace name every time I create an object or to spend cycles moving objects from the "wrong" tablespace when they inevitably get created in the default tablespace mistakenly. Personally, I'm not overly concerned if a few tens of MB of space are "wasted" when using locally managed tablespaces with automatic extent management over a hand-optimized set of tablespaces with different uniform extent sizes. On the other hand, good DBA's tend to be very concerned about things being organized "just so" so I'm not militantly opposed if a DBA wants to have separate index and data tablespaces just because that appeals to someone's sense of aesthetics.

Answer 4

One reason for using different tablespaces would be a desire to use tablespace transportation for moving data between databases. If you have a limited set of data that you want to move without having to export and import it then tablespace transport is a good option, particularly if it is important for testing reasons that the data have exactly the same physical structure as the source system (for performance analysis work, for example).