ORA-01652无法在表空间中扩展临时段 [英] ORA-01652 Unable to extend temp segment by in tablespace

问题描述

我正在创建一个像这样的表

I am creating a table like

create table tablename
as
select * for table2

我遇到了错误

ORA-01652 Unable to extend temp segment by in tablespace

当我用Google搜索时，我通常会发现ORA-01652错误，显示出一些类似的值

When I googled I usually found ORA-01652 error showing some value like

Unable to extend temp segment by 32 in tablespace

我没有得到任何这样的值.我运行了此查询

I am not getting any such value.I ran this query

select 
   fs.tablespace_name                          "Tablespace", 
   (df.totalspace - fs.freespace)              "Used MB", 
   fs.freespace                                "Free MB", 
   df.totalspace                               "Total MB", 
   round(100 * (fs.freespace / df.totalspace)) "Pct. Free" 
from 
   (select 
      tablespace_name, 
      round(sum(bytes) / 1048576) TotalSpace 
   from 
      dba_data_files 
   group by 
      tablespace_name 
   ) df, 
   (select 
      tablespace_name, 
      round(sum(bytes) / 1048576) FreeSpace 
   from 
      dba_free_space 
   group by 
      tablespace_name 
   ) fs 
where 
   df.tablespace_name = fs.tablespace_name; 

摘自:找到表空间上的可用空间

，我发现我正在使用的表空间目前大约有32Gb的可用空间.我什至尝试创建像这样的表

and I found that the tablespace I am using currently has around 32Gb of free space. I even tried creating table like

create table tablename tablespace tablespacename
as select * from table2 

但是我再次遇到相同的错误.任何人都可以给我一个想法，问题出在哪里以及如何解决.供您参考，select语句将为我提取40,000,000条记录.

but I am getting the same error again. Can anyone give me an idea, where the problem is and how to solve it. For your information the select statement would fetch me 40,000,000 records.

推荐答案

我找到了解决方案.有一个称为TEMP的临时表空间，数据库在内部使用该表空间执行诸如distinct，join等操作.由于我的查询(具有4个联接)提取了将近5000万条记录，因此TEMP表空间没有足够的空间来容纳所有数据.因此即使我的表空间有可用空间，查询也会失败.因此，在增加TEMP表空间的大小后，此问题得以解决.希望这对遇到同样问题的人有所帮助.谢谢:)

I found the solution to this. There is a temporary tablespace called TEMP which is used internally by database for operations like distinct, joins,etc. Since my query(which has 4 joins) fetches almost 50 million records the TEMP tablespace does not have that much space to occupy all data. Hence the query fails even though my tablespace has free space.So, after increasing the size of TEMP tablespace the issue was resolved. Hope this helps someone with the same issue. Thanks :)

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