When you perform a large sort and that sort operation cannot be performed in memory, then the sort is broken into pieces and those pieces are stored on disk. The location on disk is your TEMP tablespace. This ORA-1652 error is telling you that this tablespace does not have enough free space. You can add more space to the TEMP tablespace by either letting the tempfile (or datafile) extend more, or by adding another tempfile (or datafile). First, query DBA_TEMP_FILES to see if you are using tempfiles for the TEMP tablespace. If so, then add another tempfile with a command similar to the following:
ALTER TABLESPACE temp ADD TEMPFILE '/my_directory/temp02.dbf' SIZE 200M;
If the TEMP tablespace's files are seen in DBA_DATA_FILES instead, then substitute the word DATAFILE for TEMPFILE above.
This was first published in November 2004