Problem solve Get help with specific problems with your technologies, process and projects.

"ORA-1652:unable to extend temp segment by 128 in tablespace TEMP" error

I am getting the error, "ORA-1652:unable to extend temp segment by 128 in tablespace TEMP." The tablespace is the Default Temporary Tablespace with autoextend clause, and the hard disk space is almost full. Could you please suggest what the problem might be?

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