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.
Dig Deeper on Oracle database design and architecture
Related Q&A from Brian Peasland
Oracle expert Brian Peasland answers one reader's question about common pitfalls when connecting Oracle to outside programs. Continue Reading
One reader asks expert Brian Peasland a question about datafile sizes with the Oracle RMAN duplicate 10g command. Continue Reading
Managing parent table-child table relations in Oracle SQL environments is key to efficient programming. Continue Reading