Oracle 表空间最大大小“无限”并不是真正无限
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30359569/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle Tablespaces maxsize "unlimited" not really unlimited
提问by spots
I recently needed to import a .dmp into a new user I created. I also created a new tablespace for the the user with the following command:
我最近需要将 .dmp 导入到我创建的新用户中。我还使用以下命令为用户创建了一个新表空间：
create tablespace my_tablespace
datafile 'C:\My\Oracle\Install\DataFile01.dbf' size 10M
autoextend on
next 512K
maxsize unlimited;
While the import was running I got an error:
导入运行时出现错误：
ORA-01652 Unable to extend my_tablespace segment by in tablespace
When I examined the data files in the dba_data_files table, I observed the maxsize was around 34gb. Because I knew the general size of the database, I was able to import the .dmp without any issues after adding multiple datafiles to the tablespace.
当我检查 dba_data_files 表中的数据文件时，我观察到 maxsize 大约为 34gb。因为我知道数据库的一般大小，所以在向表空间添加多个数据文件后，我能够毫无问题地导入 .dmp。
Why did I need to add multiple datafiles to the tablespace when the first one I added was set to automatically grow to an unlimited size? Why was the maximum size 34gb and not unlimited? Is there a hard cap of 34gb?
当我添加的第一个数据文件设置为自动增长到无限大小时，为什么我需要向表空间添加多个数据文件？为什么最大大小是 34gb 而不是无限？有 34GB 的硬上限吗？
回答by Jon Heller
As you've discovered, and as Alex Poole pointed out, there are limits to an individual data file size. Smallfiles are limited to 128GB and bigfiles are limited to 128TB, depending on your block size. (But you do not want to change your block size just to increase those limits.) The size limit in the create tablespacecommand is only there if you want to further limit the size.
正如您所发现的，正如 Alex Poole 指出的那样，单个数据文件的大小是有限制的。小文件限制为 128GB，大文件限制为 128TB，具体取决于您的块大小。（但您不想仅仅为了增加这些限制而更改块大小。）create tablespace命令中的大小限制仅在您想进一步限制大小时才存在。
This can be a bit confusing. You probably don't care about managing files and want it to "just work". Managing database storage is always gonna be annoying, but here are some things you can do:
这可能有点令人困惑。您可能不关心管理文件并希望它“正常工作”。管理数据库存储总是很烦人，但您可以执行以下操作：
- Keep your tablespaces to a minimum.There aresome rare cases where it's helpful to partition data into lots of small tablespaces. But those rare benefits are usually outnumbered by the pain you will experience managing all those objects.
- Get in the habit of always adding more than one data file.If you're using ASM (which I wouldn't recommend if this is a local instance), then there is almost no reason not to go "crazy" when adding datafiles. Even if you're not using ASM you should still go a little crazy. As long as you set the original size to low, you're not close to the
MAX_FILESlimit, and you're not dealing with one of the special tablespaces like UNDO and TEMP, there is no penalty for adding more files. Don't worry too much about allocating more potential space than your hard-drive contains. This drives some DBAs crazy, but you have to weigh the chance of running out of OS space versus the chance of running out of space in a hundred files. (In either case, your application will crash.) - Set the
RESUMABLE_TIMEOUTparameter.Then SQL statements will be suspended, may generate an alert, will be listed inDBA_RESUMABLE, and will wait patiently for more space. This is very useful in data warehouses.
- 尽量减少表空间。这里有一些罕见的情况下，是有帮助的分区中的数据为许多小的表空间。但是，管理所有这些对象所经历的痛苦通常会超过这些罕见的好处。
- 养成总是添加多个数据文件的习惯。如果您正在使用 ASM（如果这是本地实例，我不建议这样做），那么在添加数据文件时几乎没有理由不“疯狂”。即使您不使用 ASM，您仍然应该有点疯狂。只要您将原始大小设置为低，您就不会接近
MAX_FILES限制，并且您没有处理诸如 UNDO 和 TEMP 之类的特殊表空间之一，添加更多文件不会有任何损失。不要太担心分配比硬盘驱动器包含更多的潜在空间。这让一些 DBA 发疯，但您必须权衡操作系统空间不足的可能性与一百个文件空间不足的可能性。（无论哪种情况，您的应用程序都会崩溃。） - 设置
RESUMABLE_TIMEOUT参数。然后 SQL 语句会被挂起，可能会产生一个警报，会被列在 中DBA_RESUMABLE，并会耐心等待更多的空间。这在数据仓库中非常有用。
Why is it called "UNLIMITED"?
为什么叫“无限”？
I would guess the keyword UNLIMITEDis a historical mistake. Oracle has had the same file size limitation since at least version 7, and perhaps earlier. Oracle 7 was released in 1992, when a 1GB hard drive cost $1995. Maybe every operating system at the time had a file size limitation lower than that. Perhaps it was reasonable back then to think of 128GB as "unlimited".
我猜这个关键字UNLIMITED是一个历史错误。Oracle至少从版本 7开始就有相同的文件大小限制，也许更早。Oracle 7 于 1992 年发布，当时1GB 的硬盘售价 1995 美元。也许当时每个操作系统的文件大小限制都低于此限制。也许当时认为 128GB 是“无限”是合理的。
回答by Cem
Unlimited maxsize is not enough for this operation, also your resumable timeout must be enough, you can set the value as milisecond, if you want unlimited;
无限的maxsize对于这个操作是不够的，你的可恢复超时时间也必须足够，你可以将值设置为毫秒，如果你想要无限；
alter system set resumable_timeout=0;
