InstantForum.NET v4.1.4FranklinFaces.com - Oracle & SQL Server Database Forums for all IT Professionalshttp://www.franklinfaces.com/no-reply@FranklinFaces.comThu, 09 Feb 2012 11:17:28 GMT20http://www.franklinfaces.com/Topic63-54-1.aspxYou can put temp tablespace into autoextend mode but put it with maxsize clause. If you think just for one query it is not feasible to extend temp tablespace then you can try below commands before running insert into.<br><br>[i][b]Alter session set workarea_size_policy=MANUAL;<br>alter session set sort_area_size=<memory in bytes>;[/b][/i]<br><br>Above will set a big sort area size for your session. Set it according to your requirement.Sun, 18 Jan 2009 10:25:58 GMTAdminhttp://www.franklinfaces.com/Topic63-54-1.aspxYou can set tempfile to be autoextend (check free disk space before):<br><br>[b]ALTER DATABASE TEMPFILE 'filename' AUTOEXTEND ON;[i][/i][/b]<br> <br>But I can't understand why it tries to allocate space in TEMP while INSERT AS SELECT (this shouldn't cause implicit sort)Sun, 18 Jan 2009 10:25:12 GMTAdminhttp://www.franklinfaces.com/Topic63-54-1.aspxHi,<BR><BR>Anyone can help on this issue. I have a procedure in this procedure one statement<BR><BR>INSERT INTO prod.temp select * from .........<BR><BR>This statement is giving the unable to extend temp segment by 64 in tablespace TEMP error.<BR><BR>This is a big query and when we run that procedure in development and testing environment it's running fine but in staging database it's given the trouble.Sun, 18 Jan 2009 10:24:38 GMTAdmin