oracle10g - ORA-01652 Unable to extend temp segment by in tablespace

Question

I am creating a table like

create table tablename
as
select * for table2

I am getting the error

ORA-01652 Unable to extend temp segment by in tablespace

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; 

Taken from: Find out free space on tablespace

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 

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.

Answer 1

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