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

User · Answer

You don t need to create a new datafile  you can extend your existing tablespace data files   Execute the following to determine the filename for the existing tablespace     SELECT   FROM DBA DATA FILES    Then extend the size of the datafile as follows  replace the filename with the one from the previous query      ALTER DATABASE DATAFILE  D  ORACLEXE ORADATA XE SYSTEM DBF  RESIZE 2048M

User · Answer

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

User · Answer

Create a new datafile by running the following command   alter tablespace TABLE SPACE NAME add datafile  D  oracle Oradata TEMP04 dbf                 size 2000M autoextend on

User · Answer

I encountered the same error message but don t have any access to the table like  dba free space  because I am not a dba  I use some previous answers to check available space and I still have a lot of space  However  after reducing the full table scan as many as possible  The problem is solved  My guess is that Oracle uses temp table to store the full table scan data  It the data size exceeds the limit  it will show the error   Hope this helps someone with the same issue

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

Examples related to oracle10g

Examples related to tablespace