title: persistent error - unable to extend temp segment by 128 in tablespace TEMP
body: <p>I was given a query that's running out of space in the TEMP tablespace. (see error-message below) As a quick solution to get the user back up and running, I simply added a TEMPFILE to the tablespace. (see alter statement below)  But, when I ran the query again, it still ran out of space. I can see from querying dba_temp_files that the tablespace is pretty big, and it does contain both datafiles, but I thought the MAXSIZE UNLIMITED clause would have solved my problem by not allowing the new datafile to run out of space.</p>
  
  <p>Besides rewriting the query, what would you recommend?</p>
  
  <p>Thanks!<br />
  Dave</p>
  
  <pre><code>SQL Error: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
  01652. 00000 -  "unable to extend temp segment by %s in tablespace %s"
  *Cause:    Failed to allocate an extent of the required number of blocks for
             a temporary segment in the tablespace indicated.
  *Action:   Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
             files to the tablespace indicated.
  
  ALTER TABLESPACE temp
    ADD TEMPFILE '+DATAC1/cop3/tempfile/temp01.dbf' SIZE 10g
    AUTOEXTEND ON
    NEXT 1g
    MAXSIZE UNLIMITED;
  
  SQL&gt; SELECT substr(tablespace_name,1,4) as tbls,
    2         substr(file_name,1,40) as file_name,
    3         bytes/1000000000 as gigabytes
    4    FROM dba_temp_files
    5   WHERE tablespace_name = 'TEMP'
    6  ;
  
  TBLS FILE_NAME                                 GIGABYTES
  ---- ---------------------------------------- ----------
  TEMP +DATAC1/cop3/tempfile/temp.771.915115045 21.4748365
  TEMP +DATAC1/cop3/tempfile/temp01.dbf          34.359722
  </code></pre>