ORA-01652：无法在表空间中将temp段扩展128个SYSTEM：如何扩展？ [英] ORA-01652: unable to extend temp segment by 128 in tablespace SYSTEM: How to extend?

问题描述

我有一个大的Oracle表，其中包含542512行。它有三列，当我尝试使用以下命令为它创建一个索引：

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:

  CREATE INDEX FTS_INDEX ON FILTERED_TEKLI_IIS_TABLOSU (ilAdi,ilceAdi,caddeAdi)

Oracle会出现以下错误：

Oracle gives the following error:

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.

我搜索这个错误，发现它是在Oracle没有足够的空间来存储中间执行诸如联接表，在大表上创建索引等操作时的数据。但我没有找到一个明确的解决方案。这些ALTER TABLESPACE和ADD DATAFILE命令似乎做的工作，但我不知道如何调用这些和与哪些参数。

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.

推荐答案

每个表空间都有一个或多个数据文件，用于存储数据。

Each tablespace has one or more datafiles that it uses to store data.

数据文件的最大大小取决于数据库的块大小。我相信默认情况下，每个数据文件最多有32gb。

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:

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.

我有Oracle Personal Edition 11g r2，在默认安装中它有一个8192块大小（每个数据文件32GB）。

I have Oracle Personal Edition 11g r2 and in a default install it had an 8,192 block size (32gb per data file).

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

您可以运行此查询来查找您拥有的数据文件，它们关联的表空间，以及您已经将最大文件大小设置为（不能超过上述32gb）：

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

select bytes/1024/1024 as mb_size,
       maxbytes/1024/1024 as maxsize_set,
       x.*
from   dba_data_files x

MAXSIZE_SET是您设置的最大大小数据文件。另外相关的是你是否将AUTOEXTEND选项设置为ON（它的名字意味着什么）。

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

如果数据文件的最大大小不够或自动扩展你可以简单运行：

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;

然而，如果它的大小为/接近32gb，自动扩展开启，

However if its size is at/near 32gb an autoextend is on, then yes, you do need another datafile for the tablespace:

alter tablespace system add datafile 'path_to_your_datafiles_folder\name_of_df_you_want.dbf' size 10m autoextend on maxsize unlimited;

此外，通常一个很好的/常见的做法是你有一个表空间专用于索引它似乎没有你做，因为你没有在你的create index语句中指定表空间，并且它正在使用默认的系统表空间），所以在创建这样的表空间后，你会运行像这样（在你的情况下）：

Also it is generally a good/common practice for you to have a tablespace that is dedicated to indexes (it doesn't seem that you do, because you didn't specify the tablespace in your create index statement, and it is using the default system tablespace), so after creating such a tablespace, you would run something like this (in your case):

CREATE INDEX FTS_INDEX ON FILTERED_TEKLI_IIS_TABLOSU (ilAdi,ilceAdi,caddeAdi)
tablespace name_of_ts_for_indexes

（创建表空间和数据文件后），通过：

(After creating that tablespace and a datafile for it), via:

create tablespace name_of_ts_for_indexes datafile
'c:\app\xyz\oradata\orcl\name_of_ts_for_indexes01.dbf' autoextend on maxsize unlimited nologging;

这样你的表就在一个表空间上，索引在另一个表空间。

That way your tables would be on one tablespace and the indexes on another.

这篇关于ORA-01652：无法在表空间中将temp段扩展128个SYSTEM：如何扩展？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！