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