SQL ORA-01652：无法将表空间系统中的临时段扩展 128：如何扩展？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25350703/
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
ORA-01652: unable to extend temp segment by 128 in tablespace SYSTEM: How to extend?
提问by Ufuk Can Bicici
I have a large Oracle table, which contains 542512 rows. It has three columns and when I try to create an index for it with the following command:
我有一个包含 542512 行的大型 Oracle 表。它有三列，当我尝试使用以下命令为其创建索引时：
CREATE INDEX FTS_INDEX ON FILTERED_TEKLI_IIS_TABLOSU (ilAdi,ilceAdi,caddeAdi)
Oracle gives the following error:
Oracle给出以下错误：
SQL Error: ORA-01652: unable to extend temp segment by 128 in tablespace SYSTEM
01652. 00000 - "unable to extend temp segment by %s in tablespace %s"
*Cause: Failed to allocate an extent of the required number of blocks for
a temporary segment in the tablespace indicated.
*Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
files to the tablespace indicated.
I searched for this error and found that it is produced when Oracle hasn't enough space to store intermediate data when executing operations like joining tables, creating indices etc. on large tables. But I did not found a clear solution for this. These ALTER TABLESPACE and ADD DATAFILE commands seem to do the job, but I am not sure how to call these and with which parameters. Any help would be appreciated.
我搜索了这个错误，发现它是由于 Oracle 在对大表执行连接表、创建索引等操作时没有足够的空间来存储中间数据时产生的。但是我没有找到明确的解决方案。这些 ALTER TABLESPACE 和 ADD DATAFILE 命令似乎可以完成这项工作，但我不确定如何调用它们以及使用哪些参数。任何帮助，将不胜感激。
回答by Brian DeMilia
Each tablespace has one or more datafiles that it uses to store data.
每个表空间都有一个或多个用于存储数据的数据文件。
The max size of a datafile depends on the block size of the database. I believe that, by default, that leaves with you with a max of 32gb per datafile.
数据文件的最大大小取决于数据库的块大小。我相信，默认情况下，每个数据文件最多可以保留 32GB。
To find out if the actual limit is 32gb, run the following:
要确定实际限制是否为 32GB，请运行以下命令：
select value from v$parameter where name = 'db_block_size';
Compare the result you get with the first column below, and that will indicate what your max datafile size is.
将您得到的结果与下面的第一列进行比较，这将表明您的最大数据文件大小是多少。
I have Oracle Personal Edition 11g r2 and in a default install it had an 8,192 block size (32gb per data file).
我有 Oracle 个人版 11g r2，在默认安装中，它的块大小为 8,192（每个数据文件 32gb）。
Block Sz Max Datafile Sz (Gb) Max DB Sz (Tb)
-------- -------------------- --------------
2,048 8,192 524,264
4,096 16,384 1,048,528
8,192 32,768 2,097,056
16,384 65,536 4,194,112
32,768 131,072 8,388,224
You can run this query to find what datafiles you have, what tablespaces they are associated with, and what you've currrently set the max file size to (which cannot exceed the aforementioned 32gb):
您可以运行此查询来查找您拥有的数据文件、它们关联的表空间以及您当前设置的最大文件大小（不能超过上述 32GB）：
select bytes/1024/1024 as mb_size,
maxbytes/1024/1024 as maxsize_set,
x.*
from dba_data_files x
MAXSIZE_SET is the maximum size you've set the datafile to. Also relevant is whether you've set the AUTOEXTEND option to ON (its name does what it implies).
MAXSIZE_SET 是您设置的数据文件的最大大小。同样相关的是您是否已将 AUTOEXTEND 选项设置为 ON（其名称符合其含义）。
If your datafile has a low max size or autoextend is not on you could simply run:
如果您的数据文件的最大大小较低或未启用自动扩展，您可以简单地运行：
alter database datafile 'path_to_your_file\that_file.DBF' autoextend on maxsize unlimited;
However if its size is at/near 32gb an autoextend is on, then yes, you do need another datafile for the tablespace:
但是，如果它的大小在/接近 32gb 并启用了自动扩展，那么是的，您确实需要表空间的另一个数据文件：
alter tablespace system add datafile 'path_to_your_datafiles_folder\name_of_df_you_want.dbf' size 10m autoextend on maxsize unlimited;
