Oracle 数据库是否应该有多个表空间用于数据存储？

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

提问by Kevin Babcock

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?

我的团队维护一个大约是 200GB 大小。所有数据（表、索引等）都位于单个“USERS”表空间中。这是一个坏主意吗？拥有多个表空间有什么好处，在什么情况下我想向我的数据库添加更多？

Thanks!

谢谢！

回答by Justin Cave

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.

• 将对象放在不同的表空间中没有性能优势。有一个古老的神话，即分离表和索引会带来一些性能优势。将 I/O 分布在所有可用的轴上有一个潜在的好处，但在单个表空间中使用多个数据文件比使用多个表空间更好，因为 Oracle 在不同数据文件中循环分配区，假设您的 SAN还没有做一些事情来平衡 I/O。
• 如果您有大型的静态查找/历史表，这样您只需将较小的事务表空间带入客户端站点，就可以将数据库的新副本带入客户端，这将是考虑多个表空间的一个原因。但是很少有应用程序具有这种设置。如果您必须携带所有 200 GB，那么您拥有多少个表空间都没有关系。
• 同样，如果您有大型只读对象，将它们放在只读表空间中可以大大减少备份所需的时间和空间。不过，这在数据仓库之外的实践中并不特别常见。
• 如果您的应用程序可以在没有某些对象子集的情况下运行，那么创建单独的表空间可能会有好处，这样您就可以将一个表空间脱机并进行表空间级恢复。尽管如此，很少有应用程序可以在没有一组对象的情况下运行——例如，如果您丢失了索引表空间，那么该应用程序可能就像您丢失了所有东西一样死了。
• 如果您有大量空表或大部分为空表以及许多非常大的表，从空间利用率的角度来看，具有不同范围分配策略的单独表空间可能是首选。这种情况偶尔会发生在打包应用程序中，其中任何给定的安装都使用相对较小比例的可用表，并且您不希望每个空表都分配有相对较大的范围。对于本地管理的表空间中的自动盘区管理，这往往不是主要问题，如果您想使用统一盘区，则可能更令人担忧。
• 如果不同的对象具有不同的磁盘性能优先级，并且您有不同类型的可用磁盘，则单独的表空间可以让您将不同的对象放在不同的磁盘集上。例如，在数据仓库中，您可能希望将较旧的数据放在较慢、较便宜的磁盘上，将较新的数据放在较昂贵的磁盘上。对于 OLTP 应用程序，这种情况很少发生。

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.

除非您的应用程序属于这些特殊情况之一，否则拥有单独表空间的唯一好处是吸引 DBA 的组织意识。就我个人而言，我很高兴能够避免每次创建对象时都指定表空间名称，或者当它们不可避免地在默认表空间中被错误地创建时，花费周期从“错误”表空间移动对象。就个人而言，我并不太担心在使用具有自动范围管理的本地管理表空间对一组手动优化的具有不同统一范围大小的表空间时，是否会“浪费”几十 MB 的空间。另一方面，优秀的 DBA 往往非常关心事情的组织方式“就这样”，所以我

回答by S.Lott

See http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/tspaces.htm

请参阅http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/tspaces.htm

See http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/physical.htm

参见http://download.oracle.com/docs/cd/B28359_01/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

您可以使用多个表空间来执行以下任务：

控制数据库数据的磁盘空间分配

为数据库用户分配特定的空间配额

通过使单个表空间联机或脱机来控制数据的可用性

执行部分数据库备份或恢复操作

跨设备分配数据存储以提高性能

回答by David Aldridge

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).

使用不同表空间的一个原因是希望使用表空间传输在数据库之间移动数据。如果您想要移动有限的数据集而不必导出和导入它，那么表空间传输是一个不错的选择，特别是如果出于测试原因，数据具有与源系统完全相同的物理结构（用于性能分析工作，例如）。

回答by Andrew

I strongly disagree with Justin Caves assessment. A production DBA would likely have a very different opinion.

我强烈反对 Justin Caves 的评估。生产 DBA 可能会有非常不同的意见。

Transportable tablespaces feature for moving subsets of the data between databases, without having to move the whole database.

可传输表空间功能用于在数据库之间移动数据的子集，而无需移动整个数据库。

read-only tablespaces so you're not backing up the entire database every week, which might take many hours, and you can't stand the performance hit for that amount of time, even if your limiting the rate.

只读表空间，因此您不会每周都备份整个数据库，这可能需要花费数小时，并且即使您限制了速率，您也无法忍受这段时间内的性能下降。

only backup certain tablespaces at fixed dates due to the sheer size, although many places just don't have databases this big. same reason as point above.

由于规模庞大，仅在固定日期备份某些表空间，尽管许多地方没有这么大的数据库。同上点的原因。

Depending on your application, let's say there are modules that can function independently of each other at the application side. If they each have their own set of tablespaces, you can take one apps tablespaces offline to do a reorg without affecting the other modules... they can run as normal.

根据您的应用程序，假设存在可以在应用程序端彼此独立运行的模块。如果他们每个人都有自己的一组表空间，您可以将一个应用程序表空间脱机进行重组，而不会影响其他模块……它们可以正常运行。

as for the separation of data and indexes: the traditional reason was for putting the two on different discs so they didn't compete with each other performance wise. Not so much of a problem with todays storage capabilities, like SANs, where it's all really the same storage area, but there is stillthe consideration that you're going to get contention at file header level even with locally managed tablespaces if you've got all your objects in the same tablespace where you can't locigally separate the indexes from the tables!! Even if you create 20 datafiles in one tablespace, you don't get to decide where the tables and indexes go, and then one day you notice you've got major contention at file header level because of massive activity against on table where it's indexes happen to be in the same datafile! In fact scrap that. if you've only got one ts, then you willwithout doubt experience file header contention.

至于数据和索引的分离：传统的原因是将两者放在不同的磁盘上，这样它们就不会在性能上相互竞争。现在的存储能力问题不大，比如 SAN，它们实际上都是相同的存储区域，但仍然存在考虑到如果您将所有对象都放在同一个表空间中，而您无法在本地将索引与表分开，那么即使使用本地管理的表空间，您也会在文件头级别发生争用！！即使您在一个表空间中创建了 20 个数据文件，您也无法决定表和索引的去向，然后有一天您会注意到文件头级别存在重大争用，因为针对索引所在表的大量活动碰巧在同一个数据文件中！事实上废弃那个。如果你只得到了一个TS，那么你将毫无疑问会遇到文件头争。

there are more reasons for having that logical separation, and no, it's not about the performance for the most part, it's more about the administration in a production environment.

进行这种逻辑分离有更多原因，不，这在很大程度上与性能无关，而与生产环境中的管理有关。

回答by JR.

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

S. Lott 已经给出了一个很好的列表，列出了人们可能想要将其拆分到多个表空间的一般原因。

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?

如果您可以回到过去并从头开始重做，您当然希望计划将数据库合理地划分为不同的表空间。但现在值得吗？